SQL LOADER

Control File
===========
LOAD DATA 
INFILE '/hdgstg01/prodapps/plsql/incoming/Canadian_Exchange_201701.csv'
TRUNCATE INTO TABLE CMG_TEST2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(PPA_SUBCLASS_ID,
GL_number,
REMARKS,
BIPAD,
ISSUE_CODE,
AMOUNT,
CUR_CODE)







1.)Then Place the '.CSV' file in the above mentioned Location.
('/hdgstg01/prodapps/plsql/incoming/Canadian_Exchange_201701.csv')
2.)Then always Place the .ctl (control file ) in the Bin folder depending upon the application..

(/bo/201/app/oracle/product/appsdev/appl/fnd/11.5.0/bin/LOAD_TEST.ctl)

3.)Then create the executable file according to the Control file i.e call the LOAD_test file as executablie file and assign it to the concurrent job.

4.Then Look for log and output file to find the errors if we have any....

This kind of Sql loader takes the parameter if not it will take the default File chargeback.csv



LOAD DATA
 INFILE 'chargeback.csv'
 BADFILE 'chargeback.bad'
 DISCARDFILE 'chargeback.dsc'
TRUNCATE INTO TABLE  HDG_CHARGEBACK_STG_TBL
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(CUSTOMER_NUMBER ,
 BIPAD,
 ISSUE_CODE,
 QUANTITY,

 CUSTOMER_REF_NR)

PASSING COLLECTION OBJECTS AS A INPUT PARAMETER

CREATE OR REPLACE PACKAGE SIDDHARTH_TEST AS
PROCEDURE MAIN_TEST( ERRBUF  OUT VARCHAR2,RETCODE OUT NUMBER);
END SIDDHARTH_TEST;
/

CREATE OR REPLACE PACKAGE BODY SIDDHARTH_TEST AS 
TYPE T1 IS TABLE OF SIDDHARTH%ROWTYPE INDEX BY BINARY_INTEGER;
V1 T1;
I NUMBER(10):=0;
CURSOR C1 IS SELECT * FROM SIDDHARTH WHERE ID=30;
PROCEDURE P1_TEST (RET IN C1%ROWTYPE) IS
BEGIN
fnd_file.put_line(fnd_file.LOG,'CHECK4');
I:=I+1;
V1(I).NAME:=RET.NAME;
V1(I).ID:=RET.ID;
fnd_file.put_line(fnd_file.LOG,'CHECK7');
dbms_output.put_line(V1(I).NAME||V1(I).ID);
END P1_TEST;
FUNCTION F1_TEST(PEC IN T1) RETURN NUMBER AS
Q NUMBER(10);
BEGIN
fnd_file.put_line(fnd_file.LOG,'CHECK5');
For I in PEC.first..PEC.last LOOP
fnd_file.put_line(fnd_file.LOG,'CHECK6');
SELECT ID INTO Q FROM SIDDHARTH WHERE NAME=(PEC(I).NAME);
RETURN (Q);
END LOOP;
END F1_TEST;
PROCEDURE MAIN_TEST(ERRBUF  OUT VARCHAR2,RETCODE OUT NUMBER) IS
P NUMBER(10);
BEGIN
fnd_file.put_line(fnd_file.LOG,'CHECK1');
FOR REC IN C1 LOOP
fnd_file.put_line(fnd_file.LOG,'CHECK2');
P1_TEST(REC);
P:=F1_TEST(V1);
fnd_file.put_line(fnd_file.LOG,P);
END LOOP;
fnd_file.put_line(fnd_file.LOG,'CHECK3');
END MAIN_TEST;
END SIDDHARTH_TEST;
/

Order Import Load Process

CREATE OR REPLACE PACKAGE APPS.CMG_LOAD_RET_PCK AS
/*****************************************************************************
-- +=========================================================================+
-- |              Comag Marketing Group, LLC                                 |
-- +=========================================================================+
-- |                                                                         |
-- |                                                                         |
-- |  Package Spec                                                           |
-- |                                                                         |
-- |  FILENAME:       CMG_LOAD_RET_PCK                                       |
-- |                                                                         |
-- |  DESCRIPTION:    Load Return Process                                    |
-- |                                                                         |
-- |                                                                         |
-- |Change Record:                                                           |
-- |===============                                                          |
-- |Version   Date        Author           Remarks                           |
-- |=======  ===========  ==============   ==================================|
+=========================================================================+
*****************************************************************************/

PROCEDURE main( errbuf  OUT VARCHAR2,
                retcode OUT NUMBER);
END CMG_LOAD_RET_PCK;
CREATE OR REPLACE PACKAGE BODY APPS.Cmg_Load_Ret_Pck AS
-- cursor definitions
-- this is the main cursor for this package
CURSOR c_cur
IS
SELECT /*+ FIRST_ROWS */
       hdr.adjustment_header_id,
       hdr.created_by,
       hdr.creation_date,
       hdr.last_updated_by,
       hdr.last_update_date,
       hdr.customer_ref_nr,
       hdr.tally_nr,
       hdr.adjustment_flag,
       hdr.record_interfaced,
       hdr.account_nr,
       hdr.source,
       hdr.total_copies,
       hdr.customer_id,
       hdr.customer_name,
       hdr.bill_to_site_use_id,
       hdr.ship_to_site_use_id,
       hdr.return_date,
       hdr.processed_date,
       hdr.employee_assigned_to,
       hdr.order_category,
       hdr.order_type_id,
       hdr.valid_status_flag,
       hdr.duplicate_flag,
       hdr.store_nr,
       hdr.price_list_id,
       hdr.salesrep_id,
       hdr.sales_channel_code,
       hdr.original_order_header_id,
       hdr.soh_original_system_ref,
       hrl.original_order_header_id line_orig_header_id,
       hrl.original_order_line_id,
       hrl.extended_price,
       hrl.unit_price,
       hrl.inventory_item_id,
       hrl.warehouse_id,
       hrl.line_nr,
       hrl.add_on_code,
       hrl.adjustment_line_id,
       hrl.item_nr,
       hrl.quantity,
       hrl.adjustment_flag line_adjustment_flag,
       hrl.corrected_flag,
       hrl.user_rejected_flag,
       hrl.override_flag,
       hrl.line_interfaced,
       hrl.bar_code,
       hrl.bipad,
       hrl.isbn,
       hrl.magazine,
       hrl.issue_code,
       hrl.cover_date_txt,
       hrl.en_title,
       hrl.upc,
       hrl.cover_price,
       hrl.valid_status_flag line_valid_status_flag,
       hrl.invalid_reason_code,
       NVL(hrl.netsale_revalidation_level,'BILL_TO') netsale_revalidation_level,
       hrl.sol_original_system_line_ref
  FROM hdg_ret_adj_lines_tbl hrl,
       hdg_ret_adj_header_tbl hdr
 WHERE hrl.quantity != 0
   AND hrl.adjustment_header_id     = hdr.adjustment_header_id
   AND hrl.line_interfaced          = 'N'
   AND hdr.record_interfaced        = 'N'
   AND hdr.valid_status_flag        = 'Y'
   AND hrl.adjustment_header_id in (1590281,1590269,1590264,1590262)
   AND hdr.order_category           = 'RMA'
   ORDER BY hdr.adjustment_header_id;
-- gets the original sales order header
CURSOR c_orig_sales_header( p_header_id IN  oe_order_headers.header_id%TYPE )
IS
  SELECT *
  FROM oe_order_headers
  WHERE header_id = p_header_id;
-- gets the original sales order line
-- if a return then it walks up the tree to the original
CURSOR c_orig_sales_line( p_line_id    IN  oe_order_lines.line_id%TYPE )
IS
  SELECT *
  FROM   oe_order_lines
  START WITH line_id                 = p_line_id
  CONNECT BY PRIOR reference_line_id = line_id
  ORDER BY LEVEL DESC;
-- sales order line pricing attribute
CURSOR c_line_attribs( p_header_id  IN  oe_order_headers.header_id%TYPE,
                       p_line_id    IN  oe_order_lines.line_id%TYPE )
IS
  SELECT pricing_context,
         pricing_attribute1
  FROM   oe_order_price_attribs
  WHERE  line_id   = p_line_id
  AND    header_id = p_header_id;
-- gets the order source id
CURSOR c_ord_source( p_source_name   IN  oe_order_sources.name%TYPE )
IS
SELECT order_source_id
FROM   oe_order_sources
WHERE  name = p_source_name;
-- gets the order type information
-- this will be based on the return or the customer attributes
CURSOR c_trans_type( p_type_id  IN oe_transaction_types.transaction_type_id%TYPE )
IS
  SELECT order_category_code,
         currency_code,
         transaction_type_id,
         conversion_type_code,
         accounting_rule_id,
         invoicing_rule_id
  FROM   oe_transaction_types
  WHERE  transaction_type_id = p_type_id
  AND    TRUNC(SYSDATE) BETWEEN start_date_active AND NVL(end_date_active,TRUNC(SYSDATE+1));
-- get the customer attributes
CURSOR c_cust_attribs( p_cust_id  IN ra_customers.customer_id%TYPE )
IS
  SELECT cca.price_list_id,
         cca.order_type_id,
         cca.primary_salesrep_id,
         cca.tax_code,
         qlh.currency_code
  FROM   qp_list_headers qlh,
         cmg_customer_attributes_vw cca
  WHERE  qlh.list_header_id = cca.price_list_id
  AND    cca.customer_id    = p_cust_id;
-- get the editions information for an item
CURSOR c_edition( p_item_id     IN  mtl_system_items.inventory_item_id%TYPE,
                  p_issue_code  IN  mtl_item_revisions.attribute7%TYPE )
IS
  SELECT attribute7       issue_code,
         attribute8       cover_date,
         attribute10      upc_code,
         attribute13      edition,
         TO_CHAR(effectivity_date,'DD-MON-YYYY') on_sale_date,
         attribute11      off_sale_date,
         attribute12      last_return_date
  FROM   mtl_item_revisions
  WHERE  organization_id   = Cmg_Utility_Pck.org_id('HDG')
  AND    attribute7 = p_issue_code
  AND    inventory_item_id = p_item_id
  and    nvl(attribute1,'N') ='N'
  AND    ROWNUM            = 1;
-- get the item informatoin
CURSOR c_item_info( p_item_id  IN  oe_order_lines.inventory_item_id%TYPE )
IS
  SELECT primary_uom_code,
         attribute6   bipad_code
  FROM   mtl_system_items
  WHERE  organization_id   = Cmg_Utility_Pck.org_id('HDG')
  AND    inventory_item_id = p_item_id;
-- get the default sales rep id
CURSOR salesrep_id_cur
IS
  SELECT salesrep_id
  FROM   ra_salesreps
  WHERE  UPPER(name) = 'NO SALES CREDIT';
-- get the default sales credit id
CURSOR sales_credit_type_cur
IS
  SELECT sales_credit_type_id
  FROM   oe_sales_credit_types
  WHERE  UPPER(name) = 'QUOTA SALES CREDIT';
-- get the default for bad product item
CURSOR invalid_item_cur
IS
  SELECT inventory_item_id bad_inv_item_id,
         organization_id,
         primary_uom_code
  FROM   mtl_system_items
  WHERE  description     LIKE 'INVALID PRODUCT%'
  AND    organization_id = Cmg_Utility_Pck.org_id('HDG');
-- get the error messages from the order import run
CURSOR c_intf_status( p_source_id  IN  oe_order_sources.order_source_id%TYPE,
                      p_request_id IN  fnd_concurrent_requests.request_id%TYPE )
IS
  SELECT h.orig_sys_document_ref,
         m.entity_code,
         m.message_text
  FROM   oe_processing_msgs_vl m,
         oe_headers_interface h
  WHERE  m.request_id                = h.request_id
  AND    m.order_source_id           = h.order_source_id
  AND    m.original_sys_document_ref = h.orig_sys_document_ref
  AND    ( m.TYPE                    = 'ERROR'
           OR m.TYPE IS NULL )
  AND    m.message_status_code       = 'OPEN'
  AND    h.ready_flag                = 'Y'
  AND    h.error_flag                = 'Y'
  AND    h.order_source_id           = p_source_id
  AND    h.request_id IN ( SELECT fcr.request_id
                           FROM   fnd_concurrent_requests fcr
                           START WITH fcr.parent_request_id = p_request_id
                           CONNECT BY PRIOR fcr.request_id  = fcr.parent_request_id);
-- count to see if any errors occured during the order import process
CURSOR c_err_count( p_request_id  IN  fnd_concurrent_requests.request_id%TYPE )
IS
  SELECT COUNT(*)
  FROM   oe_headers_interface ohi
  WHERE  ohi.request_id IN ( SELECT fcr.request_id
                             FROM   fnd_concurrent_requests fcr
                             START WITH fcr.parent_request_id = p_request_id
                             CONNECT BY PRIOR fcr.request_id  = fcr.parent_request_id)
  AND ohi.error_flag      = 'Y';
-- get the number of order import instances to submit
CURSOR c_num_threads
IS
  SELECT TO_NUMBER(meaning) num_threads
  FROM   hdg_lookup_tbl
  WHERE  lookup_type = 'RMA_IMPORT'
  AND    lookup_code = 'MAXIMUM THREADS';
--  Table definitions
TYPE ord_tbl IS TABLE OF c_cur%ROWTYPE
  INDEX BY BINARY_INTEGER;
TYPE source_rec IS RECORD(
  source_name    oe_order_sources.name%TYPE );
TYPE source_tbl IS TABLE OF source_rec
  INDEX BY BINARY_INTEGER;
ord_rec       ord_tbl;
src_rec       source_tbl;
-- variables
intf_head_rec                   oe_headers_interface%ROWTYPE;
intf_line_rec                   oe_lines_interface%ROWTYPE;
orig_head_rec                   c_orig_sales_header%ROWTYPE;
orig_line_rec                   c_orig_sales_line%ROWTYPE;
line_attribs_rec                c_line_attribs%ROWTYPE;
ord_type_rec                    c_trans_type%ROWTYPE;
cust_attr_rec                   c_cust_attribs%ROWTYPE;
invalid_item_rec                invalid_item_cur%ROWTYPE;
item_rec                        c_item_info%ROWTYPE;
edition_rec                     c_edition%ROWTYPE;
v_credit_type_id                oe_sales_credit_types.sales_credit_type_id%TYPE;
v_salesrep_id                   ra_salesreps.salesrep_id%TYPE;
v_hold                          hdg_ret_adj_header_tbl.adjustment_header_id%TYPE := NULL;
v_retcode                       NUMBER;
v_errbuf                        VARCHAR2(100);
v_dphase                        VARCHAR2(20) ;
v_dstatus                       VARCHAR2(20) ;
v_filename                      VARCHAR2(100);
v_price_context                 VARCHAR2(15)  := 'Upgrade Context';
v_hdr_cnt                       NUMBER        := 0;
v_returnable_qty                NUMBER;
v_err_count                     NUMBER;
v_sysdate                       DATE          := SYSDATE;
load_flag                       BOOLEAN;
i                               INTEGER;
v_request_id                    fnd_concurrent_requests.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
FUNCTION get_ord_source( p_source_name  IN  oe_order_sources.name%TYPE )
RETURN oe_order_sources.order_source_id%TYPE
IS
v_hold   oe_order_sources.order_source_id%TYPE;
BEGIN
OPEN  c_ord_source( p_source_name );
FETCH c_ord_source INTO v_hold;
CLOSE c_ord_source;
RETURN NVL(v_hold,-1);
END get_ord_source;
FUNCTION get_thread_count
RETURN NUMBER
IS
v_hold  NUMBER;
BEGIN
OPEN  c_num_threads;
FETCH c_num_threads INTO v_hold;
CLOSE c_num_threads;
RETURN v_hold;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 1;
END get_thread_count;
FUNCTION err_count( p_request_id  IN  fnd_concurrent_requests.request_id%TYPE )
RETURN NUMBER
IS
v_hold   NUMBER;
BEGIN
OPEN  c_err_count( p_request_id );
FETCH c_err_count INTO v_hold;
CLOSE c_err_count;
RETURN NVL(v_hold,0);
END err_count;
FUNCTION get_list_price( p_head  IN  oe_headers_interface%ROWTYPE,
                         p_line  IN  oe_lines_interface%ROWTYPE )
RETURN NUMBER
IS
v_hold                  NUMBER;
v_return_status         VARCHAR2(1);
v_msg_count             NUMBER;
BEGIN
Hdgoea67_Pck.hdgoea67_list_price_prc(
  p_price_list_id     => p_head.price_list_id,
  p_inventory_item_id => p_line.inventory_item_id,
  p_unit_code         => p_line.order_quantity_uom,
  p_pricing_attribute => p_line.pricing_attribute1,
  p_list_price        => v_hold,
  p_return_status     => v_return_status,
  p_msg_data          => v_msg_count );
RETURN NVL(v_hold,0);
END get_list_price;
PROCEDURE intialization
IS
BEGIN
OPEN  salesrep_id_cur;
FETCH salesrep_id_cur INTO v_salesrep_id;
CLOSE salesrep_id_cur;
OPEN  sales_credit_type_cur;
FETCH sales_credit_type_cur INTO v_credit_type_id;
CLOSE sales_credit_type_cur;
OPEN  invalid_item_cur;
FETCH invalid_item_cur INTO invalid_item_rec;
CLOSE invalid_item_cur;
EXCEPTION
  WHEN OTHERS THEN
    Cmg_Utility_Pck.write_output(' Intialization procedure failed => '||SQLERRM);
    RAISE;
END intialization;
PROCEDURE MISMATCH_ORDERTYPE_PRC IS
CURSOR RET_CUR IS
select h.adjustment_header_id,get_isspc_fnc(bipad,issue_code) ISS_PCT_ID,M.WHOLESALER_PROFITCENTER_ID,M.derived_ordertype
from hdg_ret_adj_header_tbl h,
     hdg.hdg_ret_adj_lines_tbl l,
     hdg.gen_profitcenter_mapping_tbl m,
     ra_customers c,
     apps.oe_transaction_types t
where h.customer_id=c.customer_id
and  h.adjustment_header_id=l.adjustment_header_id
and  l.line_interfaced          = 'N'
and  h.record_interfaced        = 'N'
and  h.valid_status_flag        = 'Y'
and  h.order_category           = 'RMA'
and  h.order_type_id            = t.transaction_type_id
and  substr(c.customer_name,1,1)=m.wholesaler_pc_name
and  substr(t.name,1,1)=m.derived_ordertype
and  l.invalid_reason_code is null  /* it will only look for the lines which are valid and have mixed profitcenter*/
--and h.adjustment_header_id in (1533869,1533870,1533863,1533863,1533864,1533865,1533866,1533867,1533868)
group by get_isspc_fnc(bipad,issue_code),h.adjustment_header_id,M.WHOLESALER_PROFITCENTER_ID,M.derived_ordertype
order by h.adjustment_header_id;
CURSOR GEN_PROFIT_CUR(P_ISS_PCT_ID IN VARCHAR2,P_WH_PCT_ID IN NUMBER,P_ORDER_TYPE IN VARCHAR2) IS
SELECT NVL(COUNT(*),0)
FROM HDG.GEN_PROFITCENTER_MAPPING_TBL
WHERE TO_CHAR(ISSUE_PROFITCENTER_ID)=P_ISS_PCT_ID
AND  WHOLESALER_PROFITCENTER_ID=P_WH_PCT_ID
AND  DERIVED_ORDERTYPE=P_ORDER_TYPE;
V_COUNT NUMBER;
BEGIN
FOR RET_REC IN RET_CUR LOOP
    OPEN GEN_PROFIT_CUR ( RET_REC.ISS_PCT_ID,RET_REC.WHOLESALER_PROFITCENTER_ID,RET_REC.derived_ordertype);
    FETCH GEN_PROFIT_CUR INTO V_COUNT;
      IF V_COUNT =0 AND  RET_REC.ISS_PCT_ID !='NO ISSUE PROFITCENTER' THEN
       -- DBMS_OUTPUT.PUT_LINE('Not a valid  adj id:'||ret_rec.adjustment_header_id||'   '||
         --                                               ret_rec.ISS_PCT_ID||'   '||
           --                                             ret_rec.WHOLESALER_PROFITCENTER_ID||'   '||
             --                                           ret_rec.derived_ordertype);
        UPDATE HDG.HDG_RET_ADJ_HEADER_TBL
        SET VALID_STATUS_FLAG='N'
        WHERE ADJUSTMENT_HEADER_ID=RET_REC.ADJUSTMENT_HEADER_ID;
     
        UPDATE HDG.HDG_RET_ADJ_LINES_TBL
        SET VALID_STATUS_FLAG='N'
        WHERE ADJUSTMENT_HEADER_ID=RET_REC.ADJUSTMENT_HEADER_ID;
         
      END IF;
   
     CLOSE GEN_PROFIT_CUR;
   
END LOOP;
COMMIT;
END MISMATCH_ORDERTYPE_PRC;
PROCEDURE load_source( p_name   IN  oe_order_sources.name%TYPE )
IS
v_exists       VARCHAR2(1);
BEGIN
Cmg_Utility_Pck.log_msg(' Inside load_source',p_name);
Cmg_Utility_Pck.log_msg('  Count',src_rec.COUNT);
v_exists := 'N';
IF src_rec.COUNT > 0 THEN
--   Cmg_Utility_Pck.write_output('in load_source procedure if count>0 ');
  FOR x IN src_rec.FIRST..src_rec.LAST LOOP
    IF src_rec(x).source_name = p_name THEN
      v_exists := 'Y';
    END IF;
    Cmg_Utility_Pck.write_output('v_exists '||v_exists);
  END LOOP;
END IF;
IF v_exists = 'N' THEN
  src_rec(src_rec.COUNT + 1).source_name := p_name;
END IF;
END load_source;
PROCEDURE get_edition( p_item_id     IN  mtl_system_items.inventory_item_id%TYPE,
                       p_issue_code  IN  mtl_item_revisions.attribute7%TYPE )
IS
BEGIN
OPEN  c_edition( p_item_id, p_issue_code );
FETCH c_edition INTO edition_rec;
IF ( c_edition%NOTFOUND OR c_edition%NOTFOUND IS NULL ) THEN
  edition_rec := NULL;
END IF;
CLOSE c_edition;
END get_edition;
PROCEDURE get_ord_type( p_type_id  IN oe_transaction_types.transaction_type_id%TYPE )
IS
BEGIN
OPEN  c_trans_type( p_type_id );
FETCH c_trans_type INTO ord_type_rec;
IF ( c_trans_type%NOTFOUND OR c_trans_type%NOTFOUND IS NULL ) THEN
  ord_type_rec := NULL;
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_trans_type;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_trans_type%isopen THEN
      CLOSE c_trans_type;
    END IF;
    Cmg_Utility_Pck.write_output(' Error with order type => '||p_type_id);
  WHEN OTHERS THEN
    IF c_trans_type%isopen THEN
      CLOSE c_trans_type;
    END IF;
    Cmg_Utility_Pck.write_output(' Error with order type => '||p_type_id);
END get_ord_type;
PROCEDURE get_cust_atts( p_cust_id  IN  ra_customers.customer_id%TYPE )
IS
BEGIN
OPEN  c_cust_attribs( p_cust_id );
FETCH c_cust_attribs INTO cust_attr_rec;
IF ( c_cust_attribs%NOTFOUND OR c_cust_attribs%NOTFOUND IS NULL ) THEN
  cust_attr_rec := NULL;
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_cust_attribs;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_cust_attribs%isopen THEN
      CLOSE c_cust_attribs;
    END IF;
    Cmg_Utility_Pck.write_output(' Error get_cust_atts => '||p_cust_id);
  WHEN OTHERS THEN
    IF c_cust_attribs%isopen THEN
      CLOSE c_cust_attribs;
    END IF;
    Cmg_Utility_Pck.write_output(' Error get_cust_atts => '||p_cust_id);
END get_cust_atts;
PROCEDURE get_item_info( p_item_id  IN  oe_order_lines.inventory_item_id%TYPE )
IS
BEGIN
OPEN  c_item_info( p_item_id );
FETCH c_item_info INTO item_rec;
IF ( c_item_info%NOTFOUND OR c_item_info%NOTFOUND IS NULL ) THEN
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_item_info;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_item_info%isopen THEN
      CLOSE c_item_info;
    END IF;
    Cmg_Utility_Pck.write_output(' Item info error => '||p_item_id||'-'||SQLERRM);
  WHEN OTHERS THEN
    IF c_item_info%isopen THEN
      CLOSE c_item_info;
    END IF;
    Cmg_Utility_Pck.write_output(' Item info error => '||p_item_id||'-'||SQLERRM);
 END get_item_info;
PROCEDURE get_orig_header( p_header_id  IN  oe_order_headers.header_id%TYPE )
IS
BEGIN
OPEN  c_orig_sales_header( p_header_id );
FETCH c_orig_sales_header INTO orig_head_rec;
IF ( c_orig_sales_header%NOTFOUND OR c_orig_sales_header%NOTFOUND IS NULL ) THEN
  orig_head_rec := NULL;
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_orig_sales_header;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_orig_sales_header%isopen THEN
      CLOSE c_orig_sales_header;
    END IF;
    Cmg_Utility_Pck.write_output(' Sales order header error => '||p_header_id||'-'||SQLERRM);
  WHEN OTHERS THEN
    IF c_orig_sales_header%isopen THEN
      CLOSE c_orig_sales_header;
    END IF;
    Cmg_Utility_Pck.write_output(' Sales order header error => '||p_header_id||'-'||SQLERRM);
END get_orig_header;
PROCEDURE get_orig_line( p_line_id    IN  oe_order_lines.line_id%TYPE )
IS
BEGIN
OPEN  c_orig_sales_line( p_line_id );
FETCH c_orig_sales_line INTO orig_line_rec;
IF ( c_orig_sales_line%NOTFOUND OR c_orig_sales_line%NOTFOUND IS NULL ) THEN
  orig_line_rec := NULL;
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_orig_sales_line;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_orig_sales_line%isopen THEN
      CLOSE c_orig_sales_line;
    END IF;
    Cmg_Utility_Pck.write_output(' Sales order line error => '||p_line_id||'-'||SQLERRM);
   WHEN OTHERS THEN
    IF c_orig_sales_line%isopen THEN
      CLOSE c_orig_sales_line;
    END IF;
    Cmg_Utility_Pck.write_output(' Sales order line error => '||p_line_id||'-'||SQLERRM);
END get_orig_line;
PROCEDURE get_line_attrs( p_header_id  IN  oe_order_headers.header_id%TYPE,
                          p_line_id    IN  oe_order_lines.line_id%TYPE )
IS
BEGIN
OPEN  c_line_attribs( p_header_id, p_line_id );
FETCH c_line_attribs INTO line_attribs_rec;
IF ( c_line_attribs%NOTFOUND OR c_line_attribs%NOTFOUND IS NULL ) THEN
  RAISE NO_DATA_FOUND;
END IF;
CLOSE c_line_attribs;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    IF c_line_attribs%isopen THEN
      CLOSE c_line_attribs;
    END IF;
    line_attribs_rec := NULL;
  WHEN OTHERS THEN
    IF c_line_attribs%isopen THEN
      CLOSE c_line_attribs;
    END IF;
    line_attribs_rec := NULL;
END get_line_attrs;
PROCEDURE update_hdr_status( p_rec        IN  c_cur%ROWTYPE,
                             p_status     IN  hdg_ret_adj_lines_tbl.valid_status_flag%TYPE,
                             p_interface  IN  hdg_ret_adj_header_tbl.valid_status_flag%TYPE )
IS
BEGIN
UPDATE hdg_ret_adj_header_tbl
SET    record_interfaced    = p_status,
       last_update_date     = SYSDATE,
       last_updated_by      = Fnd_Global.user_id
WHERE  adjustment_header_id = p_rec.adjustment_header_id;
END update_hdr_status;
PROCEDURE update_lns_status ( p_rec     IN  c_cur%ROWTYPE,
                              p_reason  IN  hdg_ret_adj_lines_tbl.invalid_reason_code%TYPE,
                              p_status  IN  hdg_ret_adj_lines_tbl.valid_status_flag%TYPE )
IS
BEGIN
UPDATE hdg_ret_adj_lines_tbl
SET    line_interfaced      = p_status,
       last_update_date     = SYSDATE,
       last_updated_by      = Fnd_Global.user_id
WHERE  adjustment_line_id   = p_rec.adjustment_line_id
AND    adjustment_header_id = p_rec.adjustment_header_id;
END update_lns_status;
PROCEDURE update_hdr_status( p_header_id  IN  hdg_ret_adj_header_tbl.adjustment_header_id%TYPE,
                             p_status     IN  hdg_ret_adj_lines_tbl.valid_status_flag%TYPE,
                             p_interface  IN  hdg_ret_adj_header_tbl.valid_status_flag%TYPE )
IS
BEGIN
Cmg_Utility_Pck.write_output('before update hdr status ');
UPDATE hdg_ret_adj_header_tbl
SET    record_interfaced    = p_status,
       last_update_date     = SYSDATE,
       last_updated_by      = Fnd_Global.user_id
WHERE  adjustment_header_id = p_header_id;
Cmg_Utility_Pck.write_output('After update hdr status');
END update_hdr_status;
PROCEDURE update_lns_status ( p_header_id  IN  hdg_ret_adj_header_tbl.adjustment_header_id%TYPE,
                              p_line_id    IN hdg_ret_adj_lines_tbl.adjustment_line_id%TYPE,
                              p_reason     IN  hdg_ret_adj_lines_tbl.invalid_reason_code%TYPE,
                              p_status     IN  hdg_ret_adj_lines_tbl.valid_status_flag%TYPE )
IS
BEGIN
UPDATE hdg_ret_adj_lines_tbl
SET    line_interfaced      = p_status,
       last_update_date     = SYSDATE,
       last_updated_by      = Fnd_Global.user_id
WHERE  adjustment_line_id   = p_line_id
AND    adjustment_header_id = p_header_id;
END update_lns_status;
PROCEDURE insert_header( p_rec  IN  oe_headers_interface%ROWTYPE )
IS
BEGIN
Cmg_Utility_Pck.log_msg(' Inside insert_header',p_rec.orig_sys_document_ref);
--Cmg_Utility_Pck.write_output('inside insert header');
INSERT INTO oe_headers_interface(
  created_by ,
  last_updated_by ,
  creation_date ,
  last_update_date ,
  booked_flag,
  ready_flag ,
  rejected_flag  ,
  operation_code ,
  error_flag ,
  order_source_id ,
  order_type_id ,
  order_category ,
  sold_to_org_id ,
  invoice_customer_id,
  ship_to_customer_id ,
  invoice_to_org_id,
  ship_to_org_id ,
  ship_from_org_id ,
  price_list_id ,
  salesrep_id,
  sales_channel_code ,
  orig_sys_document_ref ,
  ordered_date,
  transactional_curr_code ,
  conversion_type_code ,
  customer_payment_term_id ,
  accounting_rule_id ,
  invoicing_rule_id,
  CONTEXT ,
  attribute1,
  attribute2,
  attribute3,
  attribute4,
  attribute5,
  attribute6,
  attribute7,
  attribute8,
  attribute9,
  attribute10,
  attribute11,
  attribute12,
  attribute13,
  attribute14,
  attribute15,
  attribute16,
  attribute17,
  attribute18,
  attribute19,
  attribute20,
  global_attribute20,
  customer_po_number )
VALUES(
  p_rec.created_by ,
  p_rec.last_updated_by ,
  p_rec.creation_date ,
  p_rec.last_update_date ,
  p_rec.booked_flag,
  p_rec.ready_flag ,
  p_rec.rejected_flag  ,
  p_rec.operation_code ,
  p_rec.error_flag ,
  p_rec.order_source_id ,
  p_rec.order_type_id ,
  p_rec.order_category ,
  p_rec.sold_to_org_id ,
  p_rec.invoice_customer_id,
  p_rec.ship_to_customer_id ,
  p_rec.invoice_to_org_id,
  p_rec.ship_to_org_id ,
  p_rec.ship_from_org_id ,
  p_rec.price_list_id ,
  p_rec.salesrep_id,
  p_rec.sales_channel_code ,
  p_rec.orig_sys_document_ref ,
  p_rec.ordered_date,
  p_rec.transactional_curr_code ,
  p_rec.conversion_type_code ,
  p_rec.customer_payment_term_id ,
  p_rec.accounting_rule_id ,
  p_rec.invoicing_rule_id,
  p_rec.CONTEXT ,
  p_rec.attribute1,
  p_rec.attribute2,
  p_rec.attribute3,
  p_rec.attribute4,
  p_rec.attribute5,
  p_rec.attribute6,
  p_rec.attribute7,
  p_rec.attribute8,
  p_rec.attribute9,
  p_rec.attribute10,
  p_rec.attribute11,
  p_rec.attribute12,
  p_rec.attribute13,
  p_rec.attribute14,
  p_rec.attribute15,
  p_rec.attribute16,
  p_rec.attribute17,
  p_rec.attribute18,
  p_rec.attribute19,
  p_rec.attribute20,
  p_rec.global_attribute20,-- adjustment_header_id
  p_rec.customer_po_number );
--  Cmg_Utility_Pck.write_output('after inserting the header interface table ');
EXCEPTION
  WHEN OTHERS THEN
    Cmg_Utility_Pck.write_output(' Error creating header record => '||p_rec.orig_sys_document_ref);
    RAISE;
END insert_header;
PROCEDURE insert_line( p_rec  IN  oe_lines_interface%ROWTYPE )
IS
BEGIN
Cmg_Utility_Pck.log_msg(' Inside insert_line',p_rec.orig_sys_line_ref);
INSERT INTO oe_lines_interface(
  creation_date,
  created_by,
  last_update_date ,
  last_updated_by ,
  error_flag ,
  operation_code ,
  rejected_flag ,
  orig_sys_document_ref,
  orig_sys_line_ref,
  line_number ,
  order_quantity_uom ,
  ordered_quantity ,
  request_date,
  inventory_item_id ,
  ship_to_customer_id ,
  calculate_price_flag ,
  unit_selling_price ,
  unit_list_price ,
  order_source_id  ,
  ship_to_org_id ,
  ship_from_org_id ,
  pricing_context,
  pricing_attribute1,
  attribute1 ,
  attribute2,
  attribute3,
  attribute4,
  attribute5,
  attribute6,
  attribute7,
  attribute8,
  attribute9 ,
  attribute10,
  attribute11,
  attribute12,
  attribute13,
  attribute14,
  attribute15,
  attribute16,
  attribute17,
  attribute18,
  attribute19,
  attribute20,
  return_context,
  return_attribute1,
  return_attribute2,
  return_reason_code,
  reference_type,
  reference_header_id,
  reference_line_id,
  pricing_date,
  sold_to_org_id,
  invoice_to_org_id,
  global_attribute_category,
  global_attribute1,
  global_attribute2,
  global_attribute3,
  global_attribute4,
  global_attribute5,
  global_attribute6,
  global_attribute7,
  global_attribute8,
  global_attribute9,
  global_attribute10,
  global_attribute11,
  global_attribute12,
  global_attribute13,
  global_attribute14,
  global_attribute15,
  global_attribute16,
  global_attribute17,
  global_attribute18,
  global_attribute19,
  global_attribute20,
  industry_attribute18,
  industry_attribute19,
  industry_attribute20,
  line_category_code,-- SCR 6658
  line_type )-- SCR 6658
VALUES(
  p_rec.creation_date,
  p_rec.created_by,
  p_rec.last_update_date ,
  p_rec.last_updated_by ,
  p_rec.error_flag ,
  p_rec.operation_code ,
  p_rec.rejected_flag ,
  p_rec.orig_sys_document_ref,
  p_rec.orig_sys_line_ref,
  p_rec.line_number ,
  p_rec.order_quantity_uom ,
  p_rec.ordered_quantity ,
  p_rec.request_date,
  p_rec.inventory_item_id ,
  p_rec.ship_to_customer_id ,
  p_rec.calculate_price_flag ,
  p_rec.unit_selling_price ,
  p_rec.unit_list_price ,
  p_rec.order_source_id  ,
  p_rec.ship_to_org_id ,
  p_rec.ship_from_org_id ,
  NVL(p_rec.pricing_context,v_price_context),
  p_rec.pricing_attribute1,
  p_rec.attribute1 ,
  p_rec.attribute2,
  p_rec.attribute3,
  p_rec.attribute4,
   /* if the referenced cover price is null or 0, populate the unit list price from the referenced Sales Order line as the cover price */
  decode(p_rec.attribute5,null,p_rec.unit_list_price,
                          0,p_rec.unit_list_price,
                            p_rec.attribute5),
  p_rec.attribute6,
  p_rec.attribute7,
  p_rec.attribute8,
  p_rec.attribute9 ,
  p_rec.attribute10,
  p_rec.attribute11,
  p_rec.attribute12,
  p_rec.attribute13,
  p_rec.attribute14,
  p_rec.attribute15,
  p_rec.attribute16,
  p_rec.attribute17,
  p_rec.attribute18,
  p_rec.attribute19,
  p_rec.attribute20,
  p_rec.return_context,
  p_rec.return_attribute1,
  p_rec.return_attribute2,
  p_rec.return_reason_code,--NVL(p_rec.return_reason_code,'RETURN'), -- SCR 6658
  p_rec.reference_type,
  p_rec.reference_header_id,
  p_rec.reference_line_id,
  p_rec.pricing_date,
  p_rec.sold_to_org_id,
  p_rec.invoice_to_org_id,
  p_rec.global_attribute_category,
  p_rec.global_attribute1,
  p_rec.global_attribute2,
  p_rec.global_attribute3,
  p_rec.global_attribute4,
  p_rec.global_attribute5,
  p_rec.global_attribute6,
  p_rec.global_attribute7,
  p_rec.global_attribute8,
  p_rec.global_attribute9,
  p_rec.global_attribute10,
  p_rec.global_attribute11,
  p_rec.global_attribute12,
  p_rec.global_attribute13,
  p_rec.global_attribute14,
  p_rec.global_attribute15,
  p_rec.global_attribute16,
  p_rec.global_attribute17,
  p_rec.global_attribute18,
  p_rec.global_attribute19,
  p_rec.global_attribute20,
  p_rec.industry_attribute18,-- refrenced header_id
  p_rec.industry_attribute19,--refrenced_line_id
  p_rec.industry_attribute20, -- adjustment_line_id
  p_rec.line_category_code,-- SCR 6658
  p_rec.line_type );-- SCR 6658
IF p_rec.return_context IS NULL THEN
  INSERT INTO oe_price_atts_interface(
    created_by,
    creation_date,
    last_updated_by,
    last_update_date,
    operation_code,
    orig_sys_document_ref,
    orig_sys_line_ref,
    pricing_context,
    pricing_attribute1,
    order_source_id,
    flex_title )
  VALUES(
    Fnd_Global.user_id,
    SYSDATE,
    Fnd_Global.user_id,
    SYSDATE,
    'CREATE',
    p_rec.orig_sys_document_ref,
    p_rec.orig_sys_line_ref,
    NVL(p_rec.pricing_context,v_price_context),
    p_rec.pricing_attribute1,
    p_rec.order_source_id,
    'QP_ATTR_DEFNS_PRICING' );
END IF;
EXCEPTION
  WHEN OTHERS THEN
    Cmg_Utility_Pck.write_output(' Error creating line record => '||p_rec.orig_sys_document_ref);
    RAISE;
END insert_line;
PROCEDURE set_header( p_rec  IN  ord_tbl )
IS
BEGIN
Cmg_Utility_Pck.log_msg(' Inside set_header',p_rec(p_rec.FIRST).line_orig_header_id);
v_hdr_cnt := v_hdr_cnt + 1;
GET_ORIG_HEADER  ( p_rec(p_rec.FIRST).line_orig_header_id );
-- GET_CUST_INFO    ( p_rec(1).customer_id );
-- GET_NEW_ORD      ( cust_rec );
GET_CUST_ATTS    ( p_rec(p_rec.FIRST).customer_id );
IF p_rec(p_rec.first).order_type_id IS NULL THEN
  GET_ORD_TYPE     ( cust_attr_rec.order_type_id );
ELSE
  GET_ORD_TYPE     ( p_rec(p_rec.FIRST).order_type_id );
END IF;
intf_head_rec                          := NULL;
intf_head_rec.created_by               := p_rec(p_rec.FIRST).created_by;
intf_head_rec.last_updated_by          := p_rec(p_rec.FIRST).created_by;
intf_head_rec.creation_date            := v_sysdate;
intf_head_rec.last_update_date         := intf_head_rec.creation_date;
intf_head_rec.booked_flag              := 'Y';
intf_head_rec.ready_flag               := 'Y';
intf_head_rec.rejected_flag            := 'N';
intf_head_rec.operation_code           := 'CREATE';
-- intf_head_rec.error_flag               := 'Y';
IF p_rec(p_rec.FIRST).adjustment_flag = 'Y' THEN
           -- Since header is a manual adjustment, order source
           -- must either be EN MANUAL ADJ or HDG MANUAL ADJ
  intf_head_rec.order_source_id          := GET_ORD_SOURCE('HDG MANUAL ADJ');
  LOAD_SOURCE('HDG MANUAL ADJ');
ELSE
-- Must be a Price Change or Customer Transfer
  IF p_rec(p_rec.FIRST).soh_original_system_ref LIKE '%GPC%' THEN
    intf_head_rec.order_source_id          := GET_ORD_SOURCE('HDG AUTO PRICE ADJ');
    LOAD_SOURCE('HDG AUTO PRICE ADJ');
  ELSIF p_rec(p_rec.FIRST).soh_original_system_ref LIKE '%ACT%' THEN
    intf_head_rec.order_source_id          := GET_ORD_SOURCE('HDG CUSTOMER TRANSFERS');
    LOAD_SOURCE('HDG CUSTOMER TRANSFERS');
  ELSE
  -- Must be an electronically processed return
   Cmg_Utility_Pck.write_output('getting return  source id');
   intf_head_rec.order_source_id          := GET_ORD_SOURCE('HDG RETURNS');
    LOAD_SOURCE('HDG RETURNS');
    Cmg_Utility_Pck.write_output('after load_source hdg returns ');
  END IF;
END IF;
intf_head_rec.sold_to_org_id           := p_rec(p_rec.FIRST).customer_id;
intf_head_rec.invoice_customer_id      := p_rec(p_rec.FIRST).customer_id;
intf_head_rec.ship_to_customer_id      := p_rec(p_rec.FIRST).customer_id;
intf_head_rec.invoice_to_org_id        := p_rec(p_rec.FIRST).bill_to_site_use_id;
intf_head_rec.ship_to_org_id           := p_rec(p_rec.FIRST).ship_to_site_use_id;
intf_head_rec.ship_from_org_id         := p_rec(p_rec.FIRST).warehouse_id;
intf_head_rec.price_list_id            := NVL(p_rec(p_rec.FIRST).price_list_id,cust_attr_rec.price_list_id);
intf_head_rec.salesrep_id              := NVL(NVL(p_rec(p_rec.FIRST).salesrep_id,cust_attr_rec.primary_salesrep_id),v_salesrep_id);
intf_head_rec.sales_channel_code       := NVL(p_rec(p_rec.FIRST).sales_channel_code,'HDG DEFAULT');
intf_head_rec.ordered_date             := SYSDATE;  -- nvl(p_rec(p_rec.FIRST).processed_date,sysdate);
intf_head_rec.transactional_curr_code  := NVL(ord_type_rec.currency_code,cust_attr_rec.currency_code);
intf_head_rec.conversion_type_code     := ord_type_rec.conversion_type_code;
intf_head_rec.customer_payment_term_id := orig_head_rec.payment_term_id;
intf_head_rec.accounting_rule_id       := ord_type_rec.accounting_rule_id;
intf_head_rec.invoicing_rule_id        := ord_type_rec.invoicing_rule_id;
intf_head_rec.orig_sys_document_ref    := p_rec(p_rec.FIRST).adjustment_header_id||'-'||
                                          p_rec(p_rec.FIRST).tally_nr||'-'||
                                          p_rec(p_rec.FIRST).customer_ref_nr;
intf_head_rec.customer_po_number       := p_rec(p_rec.FIRST).customer_ref_nr;
intf_head_rec.order_type_id            := NVL(ord_type_rec.transaction_type_id,cust_attr_rec.order_type_id);
intf_head_rec.order_category           := ord_type_rec.order_category_code;
-- dff definitions
intf_head_rec.CONTEXT                  := ord_type_rec.order_category_code;
intf_head_rec.attribute1               := orig_head_rec.attribute1;
intf_head_rec.attribute2               := orig_head_rec.attribute2;
intf_head_rec.attribute3               := p_rec(p_rec.FIRST).customer_ref_nr;
intf_head_rec.attribute4               := p_rec(p_rec.FIRST).return_date;
-- SCR 6658
-- SCR 8637 ( Added %RV-% to include Genera Customer Account Returns Reversals for CMG Publishers)
IF p_rec(p_rec.FIRST).customer_ref_nr like '%RV' OR p_rec(p_rec.FIRST).customer_ref_nr like '%RV-%'then
  intf_head_rec.attribute5 := null;
ELSE
intf_head_rec.attribute5               := p_rec(p_rec.FIRST).tally_nr;
END IF;
intf_head_rec.attribute6               := orig_head_rec.attribute6;
intf_head_rec.attribute7               := orig_head_rec.attribute7;
intf_head_rec.attribute8               := orig_head_rec.attribute8;
intf_head_rec.attribute9               := orig_head_rec.attribute9;
intf_head_rec.attribute10              := orig_head_rec.attribute10;
intf_head_rec.attribute11              := orig_head_rec.attribute11;
intf_head_rec.attribute12              := orig_head_rec.attribute12;
intf_head_rec.attribute13              := orig_head_rec.attribute13;
intf_head_rec.attribute14              := orig_head_rec.attribute14;
intf_head_rec.attribute15              := orig_head_rec.attribute15;
intf_head_rec.attribute16              := orig_head_rec.attribute16;
intf_head_rec.attribute17              := orig_head_rec.attribute17;
intf_head_rec.attribute18              := orig_head_rec.attribute18;
intf_head_rec.attribute19              := orig_head_rec.attribute19;
intf_head_rec.global_attribute20       := p_rec(p_rec.FIRST).adjustment_header_id;
Cmg_Utility_Pck.write_output('Loading into header table');
INSERT_HEADER( intf_head_rec );
Cmg_Utility_Pck.write_output('After insert_header done ');
Cmg_Utility_Pck.write_output('Before update header status ');
UPDATE_HDR_STATUS( p_rec(p_rec.FIRST).adjustment_header_id, 'Y', NULL );
Cmg_Utility_Pck.write_output('after update header status ');
EXCEPTION
  WHEN OTHERS THEN
    Cmg_Utility_Pck.write_output(' set_header: Err others => '||SQLERRM);
    RAISE;
END set_header;
PROCEDURE set_line( p_rec  IN  ord_tbl )
IS
BEGIN
FOR j IN p_rec.FIRST..p_rec.LAST LOOP
  GET_ORIG_LINE  ( p_rec(j).original_order_line_id );
  GET_ITEM_INFO  ( p_rec(j).inventory_item_id );
  GET_LINE_ATTRS ( orig_line_rec.header_id, orig_line_rec.line_id );
  get_edition    ( p_rec(j).inventory_item_id, p_rec(j).issue_code );
/* Get_Edition procedure is modified by passing the necessary issue_code to get the active UPC from the revisions table */
 /* GET_EDITION    ( p_rec(j).inventory_item_id, NULL); */ -- commented out.
  intf_line_rec                       := NULL;
  intf_line_rec.creation_date         := v_sysdate;
  intf_line_rec.created_by            := p_rec(j).created_by;
  intf_line_rec.last_update_date      := v_sysdate;
  intf_line_rec.last_updated_by       := p_rec(j).created_by;
  intf_line_rec.operation_code        := 'CREATE';
  intf_line_rec.rejected_flag         := 'N';
  intf_line_rec.pricing_context       := NVL(line_attribs_rec.pricing_context,orig_line_rec.pricing_context);
  intf_line_rec.pricing_attribute1    := NVL(NVL(line_attribs_rec.pricing_attribute1,
                                                 orig_line_rec.pricing_attribute1),p_rec(j).upc);
  intf_line_rec.orig_sys_document_ref := intf_head_rec.orig_sys_document_ref;
  intf_line_rec.orig_sys_line_ref     := p_rec(j).adjustment_line_id;
  intf_line_rec.line_number           := j;
  intf_line_rec.order_quantity_uom    := item_rec.primary_uom_code;
  intf_line_rec.ordered_quantity      := p_rec(j).quantity;
  intf_line_rec.request_date          := v_sysdate;
  intf_line_rec.inventory_item_id     := p_rec(j).inventory_item_id;
  intf_line_rec.ship_to_customer_id   := intf_head_rec.ship_to_customer_id;
  intf_line_rec.attribute4            := NVL(NVL(p_rec(j).bipad,item_rec.bipad_code),orig_line_rec.attribute4); -- bipad
  intf_line_rec.calculate_price_flag  := 'N';
  intf_line_rec.unit_list_price       := NVL(NVL(orig_line_rec.attribute5,p_rec(j).cover_price),GET_LIST_PRICE( intf_head_rec, intf_line_rec ));
  intf_line_rec.attribute5            := NVL(NVL(orig_line_rec.attribute5,p_rec(j).cover_price),GET_LIST_PRICE( intf_head_rec, intf_line_rec ));
 -- intf_line_rec.unit_selling_price    := NVL(orig_line_rec.unit_selling_price,p_rec(j).unit_price);
   IF orig_line_rec.unit_selling_price = 0 OR orig_line_rec.unit_selling_price IS NULL THEN
            intf_line_rec.unit_selling_price := NVL(p_rec(j).unit_price,0);
   ELSE
        intf_line_rec.unit_selling_price :=orig_line_rec.unit_selling_price;
   END IF;
   IF (p_rec(j).invalid_reason_code IS NOT NULL ) THEN
    IF ( p_rec(j).user_rejected_flag     = 'Y' ) THEN
      intf_line_rec.unit_selling_price   := 0;
      intf_line_rec.attribute5 := NVL(NVL(orig_line_rec.attribute5,p_rec(j).cover_price),GET_LIST_PRICE( intf_head_rec, intf_line_rec ));
      intf_line_rec.unit_list_price := NVL(NVL(orig_line_rec.attribute5,p_rec(j).cover_price),GET_LIST_PRICE( intf_head_rec, intf_line_rec ));
     ELSIF  ( p_rec(j).override_flag ='Y' ) THEN
          IF (p_rec(j).invalid_reason_code IN ('NOT BILLED' ,'BAD ISSUE','BAD PRODUCT'))THEN
             intf_line_rec.calculate_price_flag := 'N';
                      IF orig_line_rec.attribute5 = 0 OR orig_line_rec.attribute5 IS NULL THEN
             intf_line_rec.unit_list_price := NVL(p_rec(j).cover_price,0);
             intf_line_rec.attribute5 := NVL(p_rec(j).cover_price,0);
           --  Cmg_Utility_Pck.write_output(' inside 0 or null  Cover price '|| p_rec(j).cover_price);
          END IF;
          IF orig_line_rec.unit_selling_price = 0 OR orig_line_rec.unit_selling_price IS NULL THEN
            intf_line_rec.unit_selling_price := NVL(p_rec(j).unit_price,0);
          --  Cmg_Utility_Pck.write_output('inside 0 or null  selling price '|| p_rec(j).unit_price );
          END IF;
            intf_line_rec.price_list_id        := cust_attr_rec.price_list_id;
         END IF;
     END IF;
   END IF;
    IF p_rec(j).invalid_reason_code  IN ('BAD PRODUCT' ,'BAD ISSUE','NOT BILLED') THEN
        intf_line_rec.attribute4 :=  NVL(p_rec(j).isbn,item_rec.bipad_code);
        intf_line_rec.inventory_item_id     := NVL(p_rec(j).inventory_item_id,invalid_item_rec.bad_inv_item_id);
    END IF;
  intf_line_rec.pricing_date          := NVL(orig_line_rec.pricing_date,orig_head_rec.ordered_date);
  intf_line_rec.order_source_id       := intf_head_rec.order_source_id;
  intf_line_rec.ship_to_org_id        := intf_head_rec.ship_to_org_id;
  intf_line_rec.ship_from_org_id      := intf_head_rec.ship_from_org_id;
  intf_line_rec.sold_to_org_id        := intf_head_rec.sold_to_org_id;
  intf_line_rec.invoice_to_org_id     := intf_head_rec.invoice_to_org_id;
-- dff definitions
  intf_line_rec.CONTEXT               := NULL;
  intf_line_rec.attribute1            := NVL(p_rec(j).issue_code,orig_line_rec.attribute1);   -- issue code
  intf_line_rec.attribute2            := NVL(orig_line_rec.attribute2,edition_rec.cover_date);  -- cover date
  intf_line_rec.attribute3            := NVL(orig_line_rec.attribute3,edition_rec.edition);  -- edition
  intf_line_rec.attribute6            := NVL(orig_line_rec.attribute6,edition_rec.on_sale_date); -- on sale date
  intf_line_rec.attribute7            := NVL(orig_line_rec.attribute7,edition_rec.off_sale_date); -- off sale date
  intf_line_rec.attribute8            := NVL(orig_line_rec.attribute8,edition_rec.last_return_date); -- last return date
  intf_line_rec.attribute9            := orig_line_rec.attribute9;  -- returnable status
  intf_line_rec.attribute10           := NULL;  -- encore interface flag
  intf_line_rec.attribute11           := orig_line_rec.attribute11;
 -- intf_line_rec.attribute12           := orig_line_rec.attribute12;
 -- SCR 6658
 -- SCR 8637 ( Added %RV-% to include GENERA Account Returns Reversals for CMG Publishers)
  IF p_rec(p_rec.FIRST).customer_ref_nr like '%RV' OR p_rec(p_rec.FIRST).customer_ref_nr like '%RV-%' then
    intf_line_rec.attribute13           := 'S';
    intf_line_rec.attribute14           :=  NULL;
    intf_line_rec.line_category_code    := 'ORDER';
    intf_line_rec.line_type             := 'CMG Order Line';
    intf_line_rec.return_reason_code    :=  NVL(p_rec(j).invalid_reason_code,null);
  ELSE
    intf_line_rec.attribute13           := NULL;
    intf_line_rec.attribute14           := NVL(p_rec(j).invalid_reason_code,'RETURN');
    intf_line_rec.line_category_code    := NULL;
    intf_line_rec.line_type             := NULL;
    intf_line_rec.return_reason_code    := NVL(p_rec(j).invalid_reason_code,'RETURN');
  END IF;
  intf_line_rec.attribute15           := orig_line_rec.attribute15;
  intf_line_rec.attribute16           := orig_line_rec.attribute16;
  intf_line_rec.attribute17           := orig_line_rec.attribute17;
  intf_line_rec.attribute18           := orig_line_rec.attribute18;
  intf_line_rec.attribute19           := orig_line_rec.attribute19;
  intf_line_rec.industry_attribute20  := p_rec(j).adjustment_line_id;
  intf_line_rec.industry_attribute18  := orig_line_rec.header_id;
  intf_line_rec.industry_attribute19  := orig_line_rec.line_id;
  INSERT_LINE( intf_line_rec );
  UPDATE_LNS_STATUS( p_rec(j).adjustment_header_id,
                     p_rec(j).adjustment_line_id,
                     NULL,
                     'Y' );
END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    Cmg_Utility_Pck.write_output(' set_line: Err others => '||SQLERRM);
    RAISE;
END set_line;
PROCEDURE load_table( p_rec  IN  c_cur%ROWTYPE )
IS
BEGIN
Cmg_Utility_Pck.log_msg(' Inside load_table',p_rec.adjustment_header_id);
IF p_rec.line_valid_status_flag = 'N' THEN
  Cmg_Utility_Pck.log_msg('  line_valid_status_flag',p_rec.line_valid_status_flag);
  load_flag := FALSE;
/*  elsif ( p_rec.override_flag = 'N' or p_rec.user_rejected_flag = 'N' ) then
  v_returnable_qty := hdg_rmaval_pck.hdg_validate_netsale_fnc(
                                      p_rec.netsale_revalidation_level,
                                      p_rec.customer_id,
                                      p_rec.inventory_item_id,
                                      p_rec.warehouse_id,
                                      p_rec.issue_code,
                                      p_rec.quantity,
                                      p_rec.ship_to_site_use_id);
  IF v_returnable_qty < 0 THEN
    cmg_utility_pck.write_output('Net Sale Validation Failed'||
                      ' for the Adj Line Id '||p_rec.adjustment_line_id);
    UPDATE_LNS_STATUS( p_rec, 'OVERDRAW', 'N' );
    UPDATE_HDR_STATUS( p_rec, 'N', null );
    load_flag := FALSE;
  end if;  */
END IF;
Cmg_Utility_Pck.write_output('Inside the load_table procedure');
i := i + 1;
ord_rec(i).adjustment_header_id         := p_rec.adjustment_header_id;
ord_rec(i).created_by                   := p_rec.created_by;
ord_rec(i).creation_date                := p_rec.creation_date;
ord_rec(i).last_updated_by              := p_rec.last_updated_by;
ord_rec(i).last_update_date             := p_rec.last_update_date;
ord_rec(i).customer_ref_nr              := p_rec.customer_ref_nr;
ord_rec(i).tally_nr                     := p_rec.tally_nr;
ord_rec(i).adjustment_flag              := p_rec.adjustment_flag;
ord_rec(i).record_interfaced            := p_rec.record_interfaced;
ord_rec(i).account_nr                   := p_rec.account_nr;
ord_rec(i).source                       := p_rec.source;
ord_rec(i).total_copies                 := p_rec.total_copies;
ord_rec(i).customer_id                  := p_rec.customer_id;
ord_rec(i).customer_name                := p_rec.customer_name;
ord_rec(i).bill_to_site_use_id          := p_rec.bill_to_site_use_id;
ord_rec(i).ship_to_site_use_id          := p_rec.ship_to_site_use_id;
ord_rec(i).return_date                  := p_rec.return_date;
ord_rec(i).processed_date               := p_rec.processed_date;
ord_rec(i).employee_assigned_to         := p_rec.employee_assigned_to;
ord_rec(i).order_category               := p_rec.order_category;
ord_rec(i).order_type_id                := p_rec.order_type_id;
ord_rec(i).valid_status_flag            := p_rec.valid_status_flag;
ord_rec(i).duplicate_flag               := p_rec.duplicate_flag;
ord_rec(i).store_nr                     := p_rec.store_nr;
ord_rec(i).price_list_id                := p_rec.price_list_id;
ord_rec(i).salesrep_id                  := p_rec.salesrep_id;
ord_rec(i).sales_channel_code           := p_rec.sales_channel_code;
ord_rec(i).original_order_header_id     := p_rec.original_order_header_id;
ord_rec(i).soh_original_system_ref      := p_rec.soh_original_system_ref;
ord_rec(i).line_orig_header_id          := p_rec.line_orig_header_id;
ord_rec(i).original_order_line_id       := p_rec.original_order_line_id;
ord_rec(i).extended_price               := p_rec.extended_price;
ord_rec(i).unit_price                   := p_rec.unit_price;
ord_rec(i).inventory_item_id            := p_rec.inventory_item_id;
ord_rec(i).warehouse_id                 := p_rec.warehouse_id;
ord_rec(i).line_nr                      := p_rec.line_nr;
ord_rec(i).add_on_code                  := p_rec.add_on_code;
ord_rec(i).adjustment_line_id           := p_rec.adjustment_line_id;
ord_rec(i).item_nr                      := p_rec.item_nr;
ord_rec(i).quantity                     := p_rec.quantity;
ord_rec(i).line_adjustment_flag         := p_rec.line_adjustment_flag;
ord_rec(i).corrected_flag               := p_rec.corrected_flag;
ord_rec(i).user_rejected_flag           := p_rec.user_rejected_flag;
ord_rec(i).override_flag                := p_rec.override_flag;
ord_rec(i).line_interfaced              := p_rec.line_interfaced;
ord_rec(i).bar_code                     := p_rec.bar_code;
ord_rec(i).bipad                        := p_rec.bipad;
ord_rec(i).isbn                         := p_rec.isbn;
ord_rec(i).magazine                     := p_rec.magazine;
ord_rec(i).issue_code                   := p_rec.issue_code;
ord_rec(i).cover_date_txt               := p_rec.cover_date_txt;
ord_rec(i).en_title                     := p_rec.en_title;
ord_rec(i).upc                          := p_rec.upc;
ord_rec(i).cover_price                  := p_rec.cover_price;
ord_rec(i).line_valid_status_flag       := p_rec.line_valid_status_flag;
ord_rec(i).invalid_reason_code          := p_rec.invalid_reason_code;
ord_rec(i).netsale_revalidation_level   := p_rec.netsale_revalidation_level;
ord_rec(i).sol_original_system_line_ref := p_rec.sol_original_system_line_ref;
--Cmg_Utility_Pck.write_output('after the load_table procedure');
END load_table;
-- start this puppy
PROCEDURE main( errbuf  OUT VARCHAR2,
                retcode OUT NUMBER) IS
BEGIN
Cmg_Utility_Pck.write_output('');
Cmg_Utility_Pck.write_output('       Log File For RMA Import Program');
Cmg_Utility_Pck.write_output('       --------------------------------------');
Cmg_Utility_Pck.write_output('');
Cmg_Utility_Pck.log_msg('CMG_LOAD_RET_PCK.MAIN: Started',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
--CHECK FOR ANY MISMATCH IN ORDER TYPES AND MARK THEM AS INVALID (FOREIGN MIGRATION)
MISMATCH_ORDERTYPE_PRC;
Cmg_Utility_Pck.write_output('1');
load_flag := FALSE;
Cmg_Utility_Pck.write_output('2');
INTIALIZATION;
Cmg_Utility_Pck.write_output('3');
FOR rec IN c_cur LOOP
--Cmg_Utility_Pck.write_output('1');
--Cmg_Utility_Pck.write_output('v_hold'||v_hold);
  -- Cmg_Utility_Pck.write_output('load flag'|| load_flag);
  Cmg_Utility_Pck.write_output('4');
  IF ( v_hold IS NULL OR v_hold <> rec.adjustment_header_id ) THEN
--  Cmg_Utility_Pck.write_output('2');
 Cmg_Utility_Pck.write_output('5');
    IF load_flag THEN
 Cmg_Utility_Pck.write_output('6');
      SET_HEADER( ord_rec );
--      Cmg_Utility_Pck.write_output('3');
      SET_LINE( ord_rec );
--Cmg_Utility_Pck.write_output('4');
    END IF;
    ord_rec.DELETE;
    load_flag       := TRUE;
    i := 0;
    v_hold := rec.adjustment_header_id;
  END IF;
--Cmg_Utility_Pck.write_output('5 ');
 Cmg_Utility_Pck.write_output('7');
  LOAD_TABLE( rec );
--  Cmg_Utility_Pck.write_output('after the load table procedure');
 -- Cmg_Utility_Pck.write_output('5-test');
END LOOP;
Cmg_Utility_Pck.write_output('8');
Cmg_Utility_Pck.log_msg(' Count',ord_rec.COUNT);
Cmg_Utility_Pck.write_output('count='||ord_rec.count);
IF ( ord_rec.COUNT > 0 AND load_flag ) THEN
--Cmg_Utility_Pck.write_output('6 ');
  SET_HEADER( ord_rec );
  SET_LINE( ord_rec );
END IF;
Cmg_Utility_Pck.write_output(' Total number of RMAs => '||v_hdr_cnt);
Cmg_Utility_Pck.write_output(' ');
--/*test
IF v_hdr_cnt > 0 THEN -- call order import
  FOR x IN src_rec.FIRST..src_rec.LAST LOOP
    Hdg_Ret_Global_Pck.HDG_ORDER_IMPORT_PRC(v_retcode,
                                            v_errbuf,
                                            v_dphase,
                                            v_dstatus,
                                            GET_ORD_SOURCE(src_rec(x).source_name),
                                            v_filename,
                                            GET_THREAD_COUNT );
    Cmg_Utility_Pck.write_output(' Following Order Import Errors Occurred ');
    Cmg_Utility_Pck.write_output(' ');
    FOR intf_rec IN c_intf_status( GET_ORD_SOURCE(src_rec(x).source_name), v_request_id ) LOOP
      Cmg_Utility_Pck.write_output(' Orig Ref => '||intf_rec.orig_sys_document_ref||
         ' Error => '||intf_rec.entity_code||'-'||intf_rec.message_text);
    END LOOP;
  END LOOP;
  Cmg_Ref_Update_Pck.UPDATE_REF( errbuf, retcode );
  v_err_count := ERR_COUNT( v_request_id );
  Cmg_Utility_Pck.write_output(' Number of processed RMAs => '||(NVL(v_hdr_cnt,0) - NVL(v_err_count,0)));
  Cmg_Utility_Pck.write_output(' Number of erred     RMAs => '||v_err_count);
  Cmg_Utility_Pck.write_output('');
END IF;
--*/ --test
COMMIT;
Cmg_Utility_Pck.write_output('---------------------------------------------------');
Cmg_Utility_Pck.write_output('End of RMA Import log file');
Cmg_Utility_Pck.log_msg('CMG_LOAD_RET_PCK.MAIN: Ended',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
retcode := NVL(retcode,SQLCODE);
errbuf  := 'CMG_LOAD_RET_PCK.MAIN: Completed'||errbuf;
EXCEPTION
 WHEN OTHERS THEN
   Cmg_Utility_Pck.write_output(SQLERRM);
   retcode := SQLCODE;
   errbuf  := SUBSTR('CMG_LOAD_RET_PCK.MAIN: Error Others => '||SQLERRM,1,150);
   ROLLBACK;
END main;
END Cmg_Load_Ret_Pck;
/

BULK COLLECT MECHANISM

declare --- Nested table
TYPE T1 is table of Siddharth%rowtype;
V1 T1:=T1();
Cursor c1 is select * from Siddharth;
begin
Open C1;
Fetch C1  bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
end;
/

declare --VARRAY
TYPE T1 is Varray(20) of Siddharth%rowtype;
V1 T1:=T1();
Cursor c1 is select * from Siddharth;
begin
Open C1;
Fetch C1  bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
end;
/


declare ---Varray
Type T1 is Varray(20) of Siddharth%rowtype;
V1 T1;
begin
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME ||V1(i).ID);
end loop;
end;
/

declare --- Nested table
TYPE T1 is table of number(10);
V1 T1:=T1(10,20,30,40,50,60,70,80,90);
begin
dbms_output.put_line(V1(1));
dbms_output.put_line(V1(2));
end;
/

declare ---Associated Array(Index by Table)
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
begin
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME ||V1(i).ID);
end loop;
end;
/


Declare--Measuring the time Performance
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
Z1 Varchar2(30);
Z2 Varchar2(30);
Cursor C1 is select * from Siddharth;
begin
Z1:=dbms_utility.get_time;
dbms_output.put_line(Z1);
open C1;
fetch  C1 bulk collect into V1;
close C1;
for i in V1.first..V1.last Loop
dbms_output.put_line(V1(i).NAME||V1(i).ID);
end loop;
Z2:=dbms_utility.get_time;
dbms_output.put_line(Z2);
dbms_output.put_line(Z1-Z2);
end;
/


DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V1 T1;
V2 T1;
Cursor C1 is select * from Siddharth;
BEGIN
Select * bulk Collect into V1 from Siddharth;
for i in V1.first..V1.last loop
UPDATE Siddharth Set Id=id+1 where name='Siddharth' returning id, name, salary bulk Collect into V2;
end Loop;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME);
end loop;
end;
/

DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V2 T1;
BEGIN
UPDATE Siddharth Set Id=id+1 where name='Siddharth' returning id,name,salary bulk Collect into V2;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME||V2(i).id);
end loop;
end;
/




DECLARE
Type T1 is Table of Siddharth%rowtype index by binary_integer;
V2 T1;
BEGIN
UPDATE Siddharth Set Id=0 where name='siddharth' returning id,name,salary  bulk Collect into V2;
for i in V2.first..V2.last loop
dbms_output.put_line(V2(i).NAME);
end loop;
end;
/



DECLARE
TYPE T1 IS RECORD (DEPARTMENT   REDDY.DEPT%TYPE,
                   ROLL_NUMBER  REDDY.ROLL_NO%TYPE);
TYPE T2 IS TABLE OF T1 INDEX BY BINARY_INTEGER;                
X1 T2;
BEGIN
X1(1).DEPARTMENT:='CHEMSITRY';
X1(1).ROLL_NUMBER:=10;
X1(2).DEPARTMENT:='PHYSICS';
X1(2).ROLL_NUMBER:=20;
FOR I IN X1.FIRST..X1.LAST LOOP
DBMS_OUTPUT.PUT_LINE(X1(I).DEPARTMENT||X1(I).ROLL_NUMBER);
END LOOP;
END;
/

Collections -Associated Arrays(index by Table),Nested Table,Varrays

Index by Table
============

1.)Declare
Type T1 is table of Siddharth%rowtype index by binary_integer;
V1 T1;
X number(10):=1;
Cursor C1 is select * from Siddharth;
BEGIN
open C1;
loop
fetch C1 into V1(X);
EXit when C1%notfound;
dbms_output.put_line(V1(X).id||V1(X).name);
X:=X+1;
end loop;
Close C1;
END;
/

2.)Declare
Type T1 is table of number(10) index by binary_integer;--Index by Table(We Cannot extend the Index and store in the database)...
V1 T1;
begin
Select id bulk Collect into V1 from Siddharth;
for X in V1.first..V1.last loop
dbms_output.put_line(V1(X));
end loop;
end;
/

SQL> DECLARE
  2    -- Associative array indexed by string:
  3  
  4    TYPE population IS TABLE OF NUMBER  -- Associative array type
  5      INDEX BY VARCHAR2(64);
  6  
  7    city_population  population;        -- Associative array variable
  8    i                VARCHAR2(64);
  9  
 10  BEGIN
 11    -- Add new elements to associative array:
 12  
 13    city_population('Smallville')  := 2000;
 14    city_population('Midland')     := 750000;
 15    city_population('Megalopolis') := 1000000;
 16  
 17    -- Change value associated with key 'Smallville':
 18  
 19    city_population('Smallville') := 2001;
 20  
 21    -- Print associative array:
 22  
 23    i := city_population.FIRST;
 24  
 25    WHILE i IS NOT NULL LOOP
 26      DBMS_Output.PUT_LINE
 27        ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
 28      i := city_population.NEXT(i);
 29    END LOOP;
 30  END;
 31  /
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001
 
PL/SQL procedure successfully completed.
 
SQL>

Collection Methods
===============
Declare
Type T1 is table of Number(10) index by binary_integer;--Index by Table
V1 T1;
begin
V1(1):=10;
V1(2):=20;
V1(3):=30;
V1(4):=40;
V1(5):=50;
dbms_output.put_line(V1.first);
dbms_output.put_line(V1.last);
dbms_output.put_line(V1.next(2));
dbms_output.put_line(V1.prior(4));
dbms_output.put_line(V1.count);
dbms_output.put_line(V1(5));
end;
/

NESTED TABLE
==============

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

VARRAYS
===========

DECLARE
TYPE T1 IS  VARRAY(5) OF INTEGER;
V1 T1;
BEGIN
V1:=T1(10,20,30,40,50);
for i in V1.first..V1.last loop
dbms_output.put_line(V1(i));
end loop;
END;
/
        (OR) -- Constructor can be assigned or declare as you like

DECLARE
TYPE T1 IS  VARRAY(5) OF INTEGER;
V1 T1:=T1(10,20,30,40,50);
BEGIN
for i in V1.first..V1.last loop
dbms_output.put_line(V1(i));
end loop;
END;
/

Here are some guidelines.

Use index by tables when:

• Your program needs small lookups
• The collection can be made at runtime in the memory when the package/ procedure is initialized
• The data volume is unknown beforehand
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
• You do not need to store the collection in the database

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database
• The number of elements of the varray is known in advance
• The data from the varray is accessed in sequence
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray