Search This Blog

Monday, May 21, 2012



SQL Loader Topics



SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.


One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:
        sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data:
        load data
          infile 'c:\data\mydata.csv'
                  into table emp
          fields terminated by "," optionally enclosed by '"'                  
          ( empno, empname, sal, deptno )
The mydata.csv file may look like this:
        10001,"Scott Tiger", 1000, 40
        10002,"Frank Naude", 500, 20
Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
        load data
          infile *
          replace
          into table departments
          (  dept     position (02:05) char(4),
             deptname position (08:27) char(20)
          )
        begindata
          COSC  COMPUTER SCIENCE
          ENGL  ENGLISH LITERATURE
          MATH  MATHEMATICS
          POLY  POLITICAL SCIENCE


Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
        set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
        spool oradata.txt
        select col1 || ',' || col2 || ',' || col3
        from   tab1
        where  col2 = 'XYZ';
        spool off
Alternatively use the UTL_FILE PL/SQL package:
        rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
        declare
           fp utl_file.file_type;
        begin
           fp := utl_file.fopen('c:\oradata','tab1.txt','w');
           utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
           utl_file.fclose(fp);
        end;
        /
You might also want to investigate third party tools like SQLWays from Ispirer Systems, TOAD from Quest, or ManageIT Fast Unloader from CA to help you unload data from Oracle.


Yes, look at the following control file examples. In the first we will load delimited data (variable length):
   LOAD DATA
   INFILE *
   INTO TABLE load_delimited_data
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
   (  data1,
      data2
   )
   BEGINDATA
   11111,AAAAAAAAAA
   22222,"A,B,C,D,"
If you need to load positional data (fixed length), look at the following control file example:
   LOAD DATA
   INFILE *
   INTO TABLE load_positional_data
   (  data1 POSITION(1:5),
      data2 POSITION(6:15)
   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB


One can skip header records or continue an interrupted load (for example if you run out of space) by specifying the "SKIP n" keyword. "n" specifies the number of logical rows to skip. Look at this example:
   LOAD DATA (SKIP 5)
   INFILE *
   INTO TABLE load_positional_data
   (  data1 POSITION(1:5),
      data2 POSITION(6:15)
   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB
If you are continuing a multiple table direct path load, you may need to use the CONTINUE_LOAD clause instead of the SKIP parameter. CONTINUE_LOAD allows you to specify a different number of rows to skip for each of the tables you are loading.


Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
   LOAD DATA
   INFILE *
   INTO TABLE modified_data
   (  rec_no                      "my_db_sequence.nextval",
      region                      CONSTANT '31',
      time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
      data1        POSITION(1:5)  ":data1/100",
      data2        POSITION(6:15) "upper(:data2)",
      data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
   )
   BEGINDATA
   11111AAAAAAAAAA991201
   22222BBBBBBBBBB990112
   LOAD DATA
   INFILE 'mail_orders.txt'
   BADFILE 'bad_orders.txt'
   APPEND
   INTO TABLE mailing_list
   FIELDS TERMINATED BY ","
   (  addr,
      city,
      state,
      zipcode,
      mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",
      mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",
      mailing_state
   )


Look at the following control file:
   LOAD DATA
   INFILE *
   REPLACE
   INTO TABLE emp
        WHEN empno != ' '
   ( empno  POSITION(1:4)   INTEGER EXTERNAL,
     ename  POSITION(6:15)  CHAR,
     deptno POSITION(17:18) CHAR,
     mgr    POSITION(20:23) INTEGER EXTERNAL
   )
   INTO TABLE proj
        WHEN projno != ' '
   (  projno POSITION(25:27) INTEGER EXTERNAL,
      empno  POSITION(1:4)   INTEGER EXTERNAL
   )


Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
   LOAD DATA
   INFILE  'mydata.dat' BADFILE  'mydata.bad' DISCARDFILE 'mydata.dis'
   APPEND
   INTO TABLE my_selective_table
   WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
   (
      region              CONSTANT '31',
      service_key         POSITION(01:11)   INTEGER EXTERNAL,
      call_b_no           POSITION(12:29)   CHAR
   )


One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
        LOAD DATA
        TRUNCATE INTO TABLE T1
        FIELDS TERMINATED BY ','
        ( field1,
          field2 FILLER,
          field3
        )


One can create one logical record from multiple physical records using one of the following two clauses:
  • CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
  • CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.


One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.


1.     A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.
2.     Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.
3.     Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.
4.     Run multiple load jobs concurrently.


SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table:
CREATE TABLE image_table (
        image_id   NUMBER(5),
        file_name  VARCHAR2(30),
        image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
 image_id   INTEGER(5),
 file_name  CHAR(30),
 image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg


The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.


AOL SYLLABUS:


PREREQUISITE

SQL
PL/SQL
D2K

GENERAL INTRODUCTION

KNOW HOW OF ERP
Version of Oracle Apps 11i
Comparison of 10.7/11.0.3/11i Version
Modules of Oracle applications
Cycle of Oracle Financials
Profiles of ERP Consultants
Front End and Back End of Apps 11i
Introduction to Tnsnames.ora files.


AOL:

  1. User Creation
·         Navigation Path
·         How to Create User in ERP
·         How to assign responsibility to the User
·         How to attached Buyer to the User
·         How to disable User
·         How to add and remove responsibility assign to User
·         Name of  table in which User Stored

  1. Responsibility
·         Navigation Path
·         What is responsibility
·         What are the three Main Component of Responsibility
·         How to Exclude Menu and Function from responsibility
·         How to disable responsibility
·         Name of  table in which Responsibility Stored
·         Difference between FND_RESPONSIBILITY and FND_RESPONSIBILITY_TL

  1. Data Group
·         Navigation Path
·         Overview of Data Group
·         Importance of Data Group
·         How to attach custom Application with Data Group
·         How to Create Custom data group
·         How to register new Schema.

  1. WHO COLUMN
  2. Applications and table Name
  3. Schema Registrations
  4. Responsibility
  5. Menu Customizations
  6. Request Group
  7. How to Register Custom Application
  8. Request Set (How to Create Request Set).
    1. How to Share Paramater in Request Set
    2. Stages of Request Set
  9. SRS WINDOW
  10. Value Set
  11. Lookups
  12. How to Get Name of table From Front End
  13. How to Register Custom tables in Oracle Applications
  14. Directory Structure of Oracle Apps
  15. How to Attach Reports in Apps
    1. Default Type in Reports
    2. Token in Reports
    3.  How to call reports through APIs
  16. How to Attach Forms in Apps
  17. Executable
  18. Concurrent Programs
  19. Incompatibility in Concurrent Programs
  20. Flex fields (Key and Descriptive Flex Fields)
  21. How to Enable DFF in the Forms
  22. Profile options
  23. User Exit
  24. Importance of P_CONC_REQUEST_ID Parameter in reports
  25. Difference between D2k Reports and APPS reports
  26. How to Register PL/SQL Procedure in apps
  27. How to Register SQL*LOADER in Oracle Applications.
  28. Telnet
  29. VI Editor
  30.  FTP
  31. Concurrent Manager
  32.  Unix Command
  33. AIM Standard for Documentations (MD50 , MD70)





Release Hold API:



CREATE OR REPLACE PROCEDURE  XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
       vreturnstatus   VARCHAR2(2);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(200);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       i               NUMBER;
BEGIN

       l_order_tbl(1).header_id := P_HEADER_ID;
       l_order_tbl(1).line_id := P_LINE_ID;
     
--   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
 
      apps.oe_holds_pub.Release_Holds (
      p_api_version        =>1.0,
      p_init_msg_list      =>apps.fnd_api.g_false,
      p_commit             =>apps.fnd_api.g_false,
      p_validation_level   =>apps.fnd_api.g_valid_level_full,
      p_order_tbl          =>l_order_tbl,
      p_hold_id            => 1,
      p_release_reason_code=> 'EXPIRE',
      p_release_comment    => 'TESTING'   ,
      x_return_status      => vreturnstatus,
      x_msg_count          => vmsgcount,
      x_msg_data           => vmsgdata);
     
      dbms_output.put_line (vreturnstatus);
     
        FOR j IN 1..OE_MSG_PUB.count_msg
            LOOP
                OE_MSG_PUB.get
                ( p_msg_index     => j
                , p_encoded       => 'F'
                , p_data          => vmsgdata
                , p_msg_index_out => i
                );
                dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
            END LOOP;
 
END XXDHL_RELEASE_HOLD_PRC ;


CREATE OR REPLACE PACKAGE BODY  XXDHL_RELEASE_HOLD_PKG AS
PROCEDURE  XXDHL_RELEASE_HOLD_PRC( P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
       vreturnstatus   VARCHAR2(2);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(200);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       i               NUMBER;
BEGIN

       l_order_tbl(1).header_id := P_HEADER_ID;
       l_order_tbl(1).line_id := P_LINE_ID;
     
--   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
 
      apps.oe_holds_pub.Release_Holds (
      p_api_version        =>1.0,
      p_init_msg_list      =>apps.fnd_api.g_false,
      p_commit             =>apps.fnd_api.g_false,
      p_validation_level   =>apps.fnd_api.g_valid_level_full,
      p_order_tbl          =>l_order_tbl,
      p_hold_id            => 1,
      p_release_reason_code=> 'EXPIRE',
      p_release_comment    => 'TESTING'   ,
      x_return_status      => vreturnstatus,
      x_msg_count          => vmsgcount,
      x_msg_data           => vmsgdata);
     
      dbms_output.put_line (vreturnstatus);
     
        FOR j IN 1..OE_MSG_PUB.count_msg
            LOOP
                OE_MSG_PUB.get
                ( p_msg_index     => j
                , p_encoded       => 'F'
                , p_data          => vmsgdata
                , p_msg_index_out => i
                );
                dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
            END LOOP;
 
END XXDHL_RELEASE_HOLD_PRC ;

FUNCTION xxdhl_credit_limit_order_prc (p_cust_id NUMBER,P_header_id NUMBER)
  RETURN VARCHAR2
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
       v_api_return_status   VARCHAR2 (1);
       vholdid            NUMBER;
       vreturnstatus   VARCHAR2(200);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(2000);
       presultout      VARCHAR2(2000);
       preturnstatus   VARCHAR2(100);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       l_include_tax_flag   VARCHAR2(1) := 'Y';
       l_order_value     NUMBER;
       l_order_tax_value  NUMBER;
       l_credit_limit   NUMBER;
       l_total_amount   NUMBER;
        l_return       VARCHAR2 (1);
        l_hold_cnt     NUMBER;
        l_FLOW_STATUS_CODE VARCHAR2(40);
        l_order_total  NUMBER;
        l_last_update_date VARCHAR2(100);

   BEGIN
    l_return := 'N';


SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';

/* select credit limit for the particular bill to customer id */
 SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
      FROM oe_order_lines_v ool
      WHERE 1=1
      AND ool.header_id =p_header_id);

/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;

/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
  FROM RA_CUSTOMER_TRX_ALL rct
  WHERE 1=1
  AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');

/* select line total for the particular line */
    SELECT    SUM(NVL(unit_selling_price,0)
                    * NVL(ordered_quantity,0))
    INTO        l_order_value
    FROM        OE_ORDER_LINES_ALL
        WHERE    1=1
        AND HEADER_ID = p_header_id;

     /* select tax total for the particular line*/
        SELECT     SUM(NVL(tax_amount,0))
    INTO        l_order_tax_value
    FROM        JA_IN_SO_TAX_LINES
        WHERE    1=1
        AND HEADER_ID = p_header_id;


       l_order_value :=l_order_value+l_order_tax_value;

       l_total_amount :=l_total_amount+l_order_value+l_order_total;

/* If total amount is greater than credit limit then return Y*/
      IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
      AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
       THEN

     l_return := 'Y';
    ELSE
     l_return := 'N';
END IF;

 RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
    RETURN l_return;
   END;

END;




CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER (P_CUST_ID NUMBER,P_HEADER_ID NUMBER);
PROCEDURE  XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;


FUNCTION xxdhl_credit_limit_order (p_cust_id NUMBER,P_header_id NUMBER)
  RETURN VARCHAR2
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
       v_api_return_status   VARCHAR2 (1);
       vholdid            NUMBER;
       vreturnstatus   VARCHAR2(200);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(2000);
       presultout      VARCHAR2(2000);
       preturnstatus   VARCHAR2(100);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       l_include_tax_flag   VARCHAR2(1) := 'Y';
       l_order_value     NUMBER;
       l_order_tax_value  NUMBER;
       l_credit_limit   NUMBER;
       l_total_amount   NUMBER;
        l_return       VARCHAR2 (1);
        l_hold_cnt     NUMBER;
        l_FLOW_STATUS_CODE VARCHAR2(40);
        l_order_total  NUMBER;
        l_last_update_date VARCHAR2(100);

   BEGIN
    l_return := 'N';


SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';

/* select credit limit for the particular bill to customer id */
 SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
      FROM oe_order_lines_v ool
      WHERE 1=1
      AND ool.header_id =p_header_id);

/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;

/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
  FROM RA_CUSTOMER_TRX_ALL rct
  WHERE 1=1
  AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');

/* select line total for the particular line */
    SELECT    SUM(NVL(unit_selling_price,0)
                    * NVL(ordered_quantity,0))
    INTO        l_order_value
    FROM        OE_ORDER_LINES_ALL
        WHERE    1=1
        AND HEADER_ID = p_header_id;

     /* select tax total for the particular line*/
        SELECT     SUM(NVL(tax_amount,0))
    INTO        l_order_tax_value
    FROM        JA_IN_SO_TAX_LINES
        WHERE    1=1
        AND HEADER_ID = p_header_id;


       l_order_value :=l_order_value+l_order_tax_value;

       l_total_amount :=l_total_amount+l_order_value+l_order_total;

/* If total amount is greater than credit limit then return Y*/
      IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
      AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
       THEN

     l_return := 'Y';
    ELSE
     l_return := 'N';
END IF;

 RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
    RETURN l_return;
   END;
 
 
/  
CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER_PRC (P_CUST_ID NUMBER,P_HEADER_ID NUMBER) RETURN VARCHAR2;
PROCEDURE  XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;
/