Oracle apps inventory query for complete information

 Oracle Application inventory query for complete details of inventory item.

SELECT     /*MTL_INVENTORY*/

mmt.transaction_date "Trans_Date",

decode(mmt.transaction_action_id),2,decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id),

                       to_char(mmt.transfer_transaction_id),5,decode(mmt.primary_quantity),-1,

   to_char(mmt.transaction_id),to_char(mmt.transfer_transaction_id)

                       ,3,to_char(mta.transaction_id),to_char(mmt.transaction_id) "Trans_ID",

 mtt.transaction_type_name "Trans_Type",

 DECODE(mta.transaction_value),1,mta.transaction_value,0,decode(mta.base_transaction_value),1,0,0,

            decode(mta.primary_quantity),1,0,0,0,NULL,NULL,NULL,decode(mta.base_transaction_value),1,

            mta.base_transaction_value,0,decode(mta.primary_quantity),1,0,0,0,NULL,NULL,NULL "Entered_Dr",  

    DECODE(mta.transaction_value), 0,decode(mta.base_transaction_value),-1,0,0,

           decode(mta.primary_quantity),-1,0,null,null,-1,(-1*mta.transaction_value),NULL,

           decode(mta.base_transaction_value),-1,(-1*mta.base_transaction_value),0,

   decode(mta.primary_quantity), -1,0,null,null,NULL"Entered_Cr"

FROM 

  gl_je_lines jel,

  gl_je_headers jeh,

  gl_import_references R,

  gl_period_statuses gps,

  gl_sets_of_books sob,

  gl_daily_conversion_types glct,

  mtl_transaction_accounts mta,

  cst_cost_elements cce,

  mtl_material_transactions mmt,

  mtl_transaction_types mtt,

  mtl_item_locations mil,

  mtl_transaction_reasons mtr,

  mtl_parameters mp,

  mtl_txn_source_types mtst,

  mfg_lookups lu1,

  mfg_lookups lu2,

  mfg_lookups lu3,

  mtl_system_items_vl msi

WHERE  

  mmt.transaction_id = mta.transaction_id AND

 (mmt.transaction_action_id NOT IN (2,3,5) OR (mmt.transaction_action_id IN (2,3,5))) AND

  mmt.primary_quantity < 0 AND

  mmt.primary_quantity = mta.primary_quantity AND

  mmt.inventory_item_id = mta.inventory_item_id AND

  mmt.organization_id = mp.organization_id AND

  mmt.organization_id = mil.organization_id AND

  mmt.locator_id = mil.inventory_location_id AND

  mmt.currency_conversion_type = glct.conversion_type AND

  mtt.transaction_type_id = mmt.transaction_type_id AND

  mtst.transaction_source_type_id = mmt.transaction_source_type_id AND

  mtr.reason_id = mmt.reason_id AND

  sob.set_of_books_id = gps.set_of_books_id AND

  gps.period_name = jel.period_name AND

  gps.application_id = 401 AND

 --mta.transaction_date BETWEEN gps.start_date AND (trunc( gps.end_date)+0.99999) AND

  msi.inventory_item_id = mmt.inventory_item_id AND

  msi.organization_id = mmt.organization_id AND

  cce.cost_element_id = mta.cost_element_id AND

  lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND

  lu1.lookup_code = mta.accounting_line_type AND

  lu2.lookup_type = 'SYS_YES_NO' AND

  lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND

  lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND

  lu3.lookup_code = 1 AND

  jeh.je_header_id = jel.je_header_id AND

  R.je_header_id = jeh.je_header_id AND

  R.je_line_num = jel.je_line_num AND

  jel.set_of_books_id = sob.set_of_books_id AND

  mta.gl_batch_id = R.reference_1 AND

  je_source = 'Inventory' AND

  je_category = 'MTL' AND

  R.gl_sl_link_id IS NOT NULL AND

  R.reference_3 IS NOT NULL AND

  mta.transaction_id = R.reference_3 AND

  (mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL) AND

  R.reference_3 IS NULL AND

  mta.reference_account = jel.code_combination_id AND

  NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND

  DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND

  NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND

  NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ')

  UNION ALL  

  SELECT 

   mmt.transaction_date "Trans_Date",

   decode(mmt.transaction_action_id), 2, decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id), 

       to_char(mmt.transfer_transaction_id), 5, decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id), 

       to_char(mmt.transfer_transaction_id), 3, to_char(mta.transaction_id),

   to_char(mmt.transaction_id) "Trans_ID",

   mtt.transaction_type_name "Trans_Type",

   DECODE(mta.transaction_value), 1,mta.transaction_value, 0, decode(mta.base_transaction_value),  

       1,0, 0, decode(mta.primary_quantity),1,0, 0,0, NULL, NULL, NULL,decode(mta.base_transaction_value),1,

   mta.base_transaction_value, 0,decode(mta.primary_quantity),1,0, 0,0, NULL, NULL, NULL "Entered_Dr",  

   DECODE(mta.transaction_value), 0,decode(mta.base_transaction_value), -1,0, 0,decode(mta.primary_quantity),

     -1,0,null, null,-1,(-1*mta.transaction_value), NULL,decode(mta.base_transaction_value), -1,

     (-1*mta.base_transaction_value),0,decode(mta.primary_quantity),-1,0,null, null, NULL "Entered_Cr" 

  FROM 

  gl_je_lines jel, 

  gl_je_headers jeh, 

  gl_import_references R, 

  gl_period_statuses gps, 

  gl_sets_of_books sob, 

  gl_daily_conversion_types glct, 

  mtl_transaction_accounts mta, 

  cst_cost_elements cce, 

  mtl_material_transactions mmt, 

  mtl_transaction_types mtt, 

  mtl_item_locations mil, 

  mtl_transaction_reasons mtr, 

  mtl_parameters mp, 

  mtl_txn_source_types mtst, 

  mfg_lookups lu1, 

  mfg_lookups lu2, 

  mfg_lookups lu3, 

  mtl_system_items_vl msi 

 WHERE  

 mmt.transfer_transaction_id = mta.transaction_id AND

 mmt.transaction_action_id IN (2,3,5) AND

 mmt.primary_quantity > 0 AND

  mmt.primary_quantity = mta.primary_quantity AND

  mmt.inventory_item_id = mta.inventory_item_id AND

  mmt.organization_id = mp.organization_id AND

  mmt.organization_id = mil.organization_id AND

  mmt.locator_id = mil.inventory_location_id AND

  mmt.currency_conversion_type = glct.conversion_type AND

  mtt.transaction_type_id = mmt.transaction_type_id AND

  mtst.transaction_source_type_id = mmt.transaction_source_type_id AND

  mtr.reason_id = mmt.reason_id AND

  sob.set_of_books_id = gps.set_of_books_id AND

  gps.period_name = jel.period_name AND

  gps.application_id = 401  AND

  --mta.transaction_date BETWEEN gps.start_date AND (trunc( gps.end_date)+0.99999) AND 

  msi.inventory_item_id = mmt.inventory_item_id AND

  msi.organization_id = mmt.organization_id AND

  --cce.cost_element_id(+) = mta.cost_element_id AND

  lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND

  lu1.lookup_code = mta.accounting_line_type AND

  lu2.lookup_type = 'SYS_YES_NO' AND

  lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND

  lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND

  lu3.lookup_code = 1 AND

  jeh.je_header_id = jel.je_header_id AND

  R.je_header_id = jeh.je_header_id AND

  R.je_line_num = jel.je_line_num AND

  jel.set_of_books_id = sob.set_of_books_id AND

  mta.gl_batch_id = R.reference_1 AND

  je_source = 'Inventory' AND

  je_category = 'MTL' AND

  R.gl_sl_link_id IS NOT NULL AND

  R.reference_3 IS NOT NULL AND

  mta.transaction_id = R.reference_3 AND

  (mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL) AND

  R.reference_3 IS NULL AND

  mta.reference_account = jel.code_combination_id AND

  NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND

  DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND

  NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND

  NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ') 

 UNION ALL

 SELECT   

  wt.transaction_date "Trans_Date",

  wt.transaction_id "Trans_ID",

  lu1.meaning "Trans_Type",  

  DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),1,nvl(wta.transaction_value,wta.base_transaction_value),0,0,NULL) "Entered_Dr",

  DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),-1,(-1*nvl(wta.transaction_value,wta.base_transaction_value)),NULL) "Entered_Cr"

FROM 

  gl_je_lines jel,

  gl_je_headers jeh,

  gl_import_references R,

  gl_period_statuses gps,

  gl_sets_of_books sob,

  gl_daily_conversion_types glct,

  wip_transaction_accounts wta,

  wip_transactions wt,

  wip_entities we,

  wip_lines wl,

  cst_cost_elements cce,

  wip_flow_schedules wfs,

  po_headers poh,

  bom_resources br,

  mtl_transaction_reasons mtr,

  bom_departments bd,

  org_organization_definitions ood,

  mtl_system_items_kfv msik,

  mfg_lookups lu1,

  mfg_lookups lu2,

  mfg_lookups lu3,

  mfg_lookups lu4,

  mfg_lookups lu5

WHERE --wt.transaction_id=6454302 AND --6454308  AND --6454302

  wta.transaction_id = wt.transaction_id AND

  we.wip_entity_id = wt.wip_entity_id AND

  wl.line_id(+) = wt.line_id AND

  we.organization_id = wt.organization_id AND

  bd.department_id(+) = wt.department_id AND

  mtr.reason_id(+) = wt.reason_id AND

  poh.po_header_id(+) = wt.po_header_id AND

  cce.cost_element_id = wta.cost_element_id AND

  br.resource_id(+) = wta.resource_id AND

  wfs.wip_entity_id (+) = we.wip_entity_id AND

  lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE' AND

  lu1.lookup_code(+) = wt.transaction_type AND

  lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND

  lu2.lookup_code = wta.accounting_line_type AND

  lu3.lookup_code(+) = wta.basis_type AND

  lu3.lookup_type(+) = 'CST_BASIS' AND

  lu4.lookup_code = 2 AND

  lu4.lookup_type = 'CST_VIEW_ACCOUNTING' AND

  lu5.lookup_type = 'SYS_YES_NO' AND

  lu5.lookup_code = decode(wta.gl_batch_id, -1, 2, 1) AND

  sob.set_of_books_id = ood.set_of_books_id AND

  gps.application_id = 401 AND

  gps.set_of_books_id = ood.set_of_books_id AND

  gps.period_name = jeh.period_name  AND

  --wta.transaction_date BETWEEN gps.start_date AND (trunc(gps.end_date)+0.99999) AND

  wt.currency_conversion_type = glct.conversion_type(+) AND

  wt.organization_id = ood.organization_id AND

  jel.je_header_id = jeh.je_header_id AND

  R.je_header_id = jeh.je_header_id AND

  R.je_line_num = jel.je_line_num AND

  sob.set_of_books_id = jel.set_of_books_id AND

  R.gl_sl_link_id IS NOT NULL AND

  R.reference_3 IS NOT NULL AND

  wta.gl_batch_id = R.reference_1 AND

  wta.transaction_id = R.reference_3 AND

  wta.reference_account = jel.code_combination_id AND

  wta.GL_SL_LINK_ID = R.gl_sl_link_id AND

  jeh.je_source = 'Inventory' AND

  jeh.je_category = 'WIP' AND

  msik.inventory_item_id(+) = we.primary_item_id AND

  msik.organization_id(+) = we.organization_id 

  UNION ALL 

  SELECT 

  wt.transaction_date "Trans_Date", 

  wt.transaction_id "Trans_ID", 

  lu1.meaning "Trans_Type", 

  DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),

                     1,nvl(wta.transaction_value,wta.base_transaction_value), 0,0,NULL) "Entered_Dr", 

  DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)), -1,

                     (-1*nvl(wta.transaction_value,wta.base_transaction_value)), NULL) "Entered_Cr" 

  FROM 

  gl_je_lines jel, 

  gl_je_headers jeh, 

  gl_import_references R, 

  gl_period_statuses gps, 

  gl_sets_of_books sob, 

  gl_daily_conversion_types glct, 

  wip_transaction_accounts wta, 

  wip_transactions wt, 

  wip_entities we, 

  wip_lines wl, 

  cst_cost_elements cce, 

  wip_flow_schedules wfs, 

  po_headers poh, 

  bom_resources br, 

  mtl_transaction_reasons mtr, 

  bom_departments bd, 

  org_organization_definitions ood, 

  mtl_system_items_kfv msik, 

  mfg_lookups lu1, 

  mfg_lookups lu2, 

  mfg_lookups lu3, 

  mfg_lookups lu4, 

  mfg_lookups lu5 

WHERE  

  wta.transaction_id = wt.transaction_id AND

  we.wip_entity_id = wt.wip_entity_id AND

  wl.line_id(+) = wt.line_id AND

  we.organization_id = wt.organization_id AND

  bd.department_id(+) = wt.department_id AND

  mtr.reason_id(+) = wt.reason_id AND

  poh.po_header_id(+) = wt.po_header_id AND

  cce.cost_element_id(+) = wta.cost_element_id AND

  br.resource_id(+) = wta.resource_id AND

  wfs.wip_entity_id (+) = we.wip_entity_id AND

  lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE' AND

  lu1.lookup_code(+) = wt.transaction_type AND

  lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND

  lu2.lookup_code = wta.accounting_line_type AND

  lu3.lookup_code(+) = wta.basis_type AND

  lu3.lookup_type(+) = 'CST_BASIS' AND

  lu4.lookup_code = 2 AND

  lu4.lookup_type = 'CST_VIEW_ACCOUNTING' AND

  lu5.lookup_type = 'SYS_YES_NO' AND

  lu5.lookup_code = decode(wta.gl_batch_id, -1, 2, 1) AND

  sob.set_of_books_id = ood.set_of_books_id AND

  gps.application_id = 401 AND

  gps.set_of_books_id = ood.set_of_books_id AND

  gps.period_name = jeh.period_name  AND

  --wta.transaction_date BETWEEN gps.start_date AND gps.end_date AND

  wt.currency_conversion_type = glct.conversion_type(+) AND

  wt.organization_id = ood.organization_id AND

  jel.je_header_id = jeh.je_header_id AND

  R.je_header_id = jeh.je_header_id AND

  R.je_line_num = jel.je_line_num AND

  sob.set_of_books_id = jel.set_of_books_id AND

  wta.gl_batch_id = R.reference_1 AND

  R.gl_sl_link_id IS NULL AND

  R.reference_3 IS NULL AND

  wta.reference_account = jel.code_combination_id AND

  nvl(wta.currency_code,sob.currency_code) = jeh.currency_code AND

  jeh.je_source = 'Inventory' AND

  jeh.je_category = 'WIP' AND

  msik.inventory_item_id(+) = we.primary_item_id AND

  msik.organization_id(+) = we.organization_id

/

Oracle Application Order creation API

 The procedure uses Oe_Order_Pub.Process_Order to process sales order.

CREATE OR REPLACE

PROCEDURE      xx_OM_PROC 

( p_org_id IN NUMBER default 204, 

p_ord_type_id IN NUMBER default 1437,

p_sold_to_org_id IN NUMBER default 1290,

p_ship_to_org_id IN NUMBER default 1425    

)

is

k NUMBER;    

P_USER_ID NUMBER;

P_RESP_ID NUMBER;

P_RESP_APPL_ID NUMBER;

l_org_id NUMBER;

p_price NUMBER;

--p_inv_s_id NUMBER;

p_line_type_id NUMBER;

p_order_number number;

l_responsibility_id NUMBER;

P_PRICE_U NUMBER;

x_return_status VARCHAR2(1);                                                                                                                                                                   

x_msg_count NUMBER(20);                                                                                                                                                                               

x_msg_data VARCHAR2(100); 

l_return_status VARCHAR2 (1000);

l_msg_count NUMBER (20);

l_msg_data VARCHAR2 (1000);  

l_msg_index_out NUMBER(10);

l_Header_Rec_Type oe_order_pub.Header_Rec_Type;

l_header_val_rec oe_order_pub.Header_Val_Rec_Type ;

l_Header_Adj_tbl oe_order_pub.Header_Adj_Tbl_Type ;

l_Header_Adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type ;

l_Header_price_Att_tbl oe_order_pub.Header_Price_Att_Tbl_Type ;

l_Header_Adj_Att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type ;

l_Header_Adj_Assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type ;

l_Header_Scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type ;

l_Header_Scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type ;

l_line_tbl oe_order_pub.Line_Tbl_Type;

l_line_val_tbl oe_order_pub.Line_Val_Tbl_Type ;

l_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type ;

l_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type ;

l_Line_price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type ;

l_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type ;

l_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type ;

l_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type ;

l_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type ;

l_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type ;

l_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type ;

l_action_request_tbl oe_order_pub.Request_Tbl_Type ;

l_line_rec   oe_order_pub.Line_Rec_Type;

--------------output----------------------

x_header_rec oe_order_pub.Header_Rec_Type;

x_header_val_rec oe_order_pub.Header_Val_Rec_Type;

x_Header_Adj_tbl oe_order_pub.Header_Adj_Tbl_Type;

x_Header_Adj_val_tbl oe_order_pub.Header_Adj_Val_Tbl_Type;

x_Header_price_Att_tbl oe_order_pub.Header_Price_Att_Tbl_Type;

x_Header_Adj_Att_tbl oe_order_pub.Header_Adj_Att_Tbl_Type;

x_Header_Adj_Assoc_tbl oe_order_pub.Header_Adj_Assoc_Tbl_Type;

x_Header_Scredit_tbl oe_order_pub.Header_Scredit_Tbl_Type;

x_Header_Scredit_val_tbl oe_order_pub.Header_Scredit_Val_Tbl_Type;

x_line_tbl oe_order_pub.Line_Tbl_Type;

x_line_val_tbl oe_order_pub.Line_Val_Tbl_Type;

x_Line_Adj_tbl oe_order_pub.Line_Adj_Tbl_Type;

x_Line_Adj_val_tbl oe_order_pub.Line_Adj_Val_Tbl_Type;

x_Line_price_Att_tbl oe_order_pub.Line_Price_Att_Tbl_Type;

x_Line_Adj_Att_tbl oe_order_pub.Line_Adj_Att_Tbl_Type;

x_Line_Adj_Assoc_tbl oe_order_pub.Line_Adj_Assoc_Tbl_Type;

x_Line_Scredit_tbl oe_order_pub.Line_Scredit_Tbl_Type;

x_Line_Scredit_val_tbl oe_order_pub.Line_Scredit_Val_Tbl_Type;

x_Lot_Serial_tbl oe_order_pub.Lot_Serial_Tbl_Type;

x_Lot_Serial_val_tbl oe_order_pub.Lot_Serial_Val_Tbl_Type;

x_action_request_tbl oe_order_pub.Request_Tbl_Type;

begin 

l_header_val_rec := oe_order_pub.G_MISS_HEADER_VAL_REC;

l_Header_Adj_tbl :=oe_order_pub.G_MISS_HEADER_ADJ_TBL;

l_Header_Adj_val_tbl :=oe_order_pub.G_MISS_HEADER_ADJ_VAL_TBL;

l_Header_price_Att_tbl :=oe_order_pub.G_MISS_HEADER_PRICE_ATT_TBL;

l_Header_Adj_Att_tbl :=oe_order_pub.G_MISS_HEADER_ADJ_ATT_TBL;

l_Header_Adj_Assoc_tbl :=oe_order_pub.G_MISS_HEADER_ADJ_ASSOC_TBL;

l_Header_Scredit_tbl :=oe_order_pub.G_MISS_HEADER_SCREDIT_TBL;

l_Header_Scredit_val_tbl :=oe_order_pub.G_MISS_HEADER_SCREDIT_VAL_TBL;

l_line_val_tbl :=oe_order_pub.G_MISS_LINE_VAL_TBL;

l_Line_Adj_tbl :=oe_order_pub.G_MISS_LINE_ADJ_TBL;

l_Line_Adj_val_tbl :=oe_order_pub.G_MISS_LINE_ADJ_VAL_TBL;

l_Line_price_Att_tbl :=oe_order_pub.G_MISS_LINE_PRICE_ATT_TBL;

l_Line_Adj_Att_tbl :=oe_order_pub.G_MISS_LINE_ADJ_ATT_TBL;

l_Line_Adj_Assoc_tbl :=oe_order_pub.G_MISS_LINE_ADJ_ASSOC_TBL;

l_Line_Scredit_tbl :=oe_order_pub.G_MISS_LINE_SCREDIT_TBL;

l_Line_Scredit_val_tbl :=oe_order_pub.G_MISS_LINE_SCREDIT_VAL_TBL;

l_Lot_Serial_tbl :=oe_order_pub.G_MISS_LOT_SERIAL_TBL;

l_Lot_Serial_val_tbl :=oe_order_pub.G_MISS_LOT_SERIAL_VAL_TBL;

l_action_request_tbl := oe_order_pub.G_MISS_REQUEST_TBL ;  

l_return_status := null;

l_msg_count := 0;

l_msg_data := null;  

l_msg_index_out := 0; 

x_msg_count :=0; 

x_msg_data :=null;                                                                                                                                    

SELECT fnd_global.USER_ID,fnd_global.RESP_APPL_ID,fnd_global.RESP_ID

        INTO P_USER_ID,P_RESP_APPL_ID,P_RESP_ID FROM dual;

P_USER_ID:=xx;

P_RESP_APPL_ID:=xx;

P_RESP_ID:= xx;

DBMS_OUTPUT.put_line( 'USER ID :      '||P_USER_ID);

DBMS_OUTPUT.put_line( 'RESP APPL ID : '||P_RESP_APPL_ID);

DBMS_OUTPUT.put_line( 'RESP ID :      '||P_RESP_ID);

l_responsibility_id:= P_RESP_ID;

DBMS_OUTPUT.put_line( 'AFTER FINDING RESPONSIBILITY ID ... ' || l_responsibility_id);                                                                                                    

l_org_id:= p_org_id; 

        --apps.mo_global.set_org_context(7269,null,'ONT');

FND_GLOBAL.APPS_INITIALIZE(xx);

fnd_client_info.set_org_context(l_org_id);

SELECT DISTINCT

PRICE_LIST_ID,

--INVOICE_SOURCE_ID,

        DEFAULT_OUTBOUND_LINE_TYPE_ID                                                                         

          INTO    p_price,

--p_inv_s_id,

p_line_type_id                                                                                                

FROM apps.oe_transaction_types_all                                                                   

WHERE TRANSACTION_TYPE_ID = 1437; 

select FND_DOC_SEQ_624_S.nextval into p_order_number from dual ;                                                                                                                             

DBMS_OUTPUT.put_line( 'PRICE LIST ID :      ' || p_price);   

--DBMS_OUTPUT.put_line( 'INVOICE_SOURCE_ID :  ' || p_inv_s_id);   

DBMS_OUTPUT.put_line( 'LINE_TYPE_ID :       ' || p_line_type_id);

DBMS_OUTPUT.put_line( 'p_order_number :       ' || p_order_number);

l_line_tbl := OE_ORDER_PUB.G_MISS_LINE_TBL;                                                                         

l_Header_Rec_Type := OE_ORDER_PUB.G_MISS_HEADER_REC; 

l_Header_Rec_Type.order_type_id := P_ORD_TYPE_ID;

l_Header_Rec_Type.sold_to_org_id := p_sold_to_org_id;

l_Header_Rec_Type.ship_to_org_id := p_ship_to_org_id;

l_Header_Rec_Type.sold_from_org_id := 204;

l_Header_Rec_Type.ship_from_org_id := 207;

l_Header_Rec_Type.salesrep_id := -3;

l_Header_Rec_Type.Minisite_Id :=null;

l_Header_Rec_Type.booked_date :=sysdate;

l_Header_Rec_Type.booked_flag := 'N';

        l_header_rec_type.open_flag:='Y';

        l_header_rec_type.cancelled_flag:='N';

l_Header_Rec_Type.flow_status_code := 'ENTERED';

l_Header_Rec_Type.booked_date := null;

l_Header_Rec_Type.order_number  := p_order_number;

l_Header_Rec_Type.payment_term_id := 4;

l_Header_Rec_Type.transactional_curr_code := 'USD'; 

l_Header_Rec_Type.price_list_id := P_PRICE;

l_Header_Rec_Type.order_source_id := 0;

l_Header_Rec_Type.org_id := l_org_id;

l_Header_Rec_Type.ordered_date   :=SYSDATE;

l_Header_Rec_Type.pricing_date := SYSDATE;

l_Header_Rec_Type.order_category_code := 'MIXED';  

l_Header_Rec_Type.operation := OE_GLOBALS.G_OPR_CREATE;

        --l_header_val_rec.order_type:='Mixed';

        l_header_rec_type.attribute15 := sysdate;

        l_Header_Rec_type.created_by  := fnd_global.user_id;

        l_Header_Rec_type.creation_date:=sysdate;

        l_Header_Rec_type.price_list_id :=1000;

        l_Header_Rec_type.freight_terms_code := NULL;

l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_Tbl(1).Operation := OE_GLOBALS.G_OPR_CREATE;

        l_line_tbl(1).inventory_item_id := 149;

l_line_tbl(1).ordered_item := 'AS54888';

l_line_tbl(1).ordered_item_id := 149;

l_line_tbl(1).sold_from_org_id := 204;

l_line_tbl(1).ship_from_org_id := 207;

l_line_tbl(1).ordered_quantity := 5; 

  l_line_tbl(1).order_quantity_uom := 'Ea'; 

l_line_tbl(1).orig_sys_document_ref := 'OE_ORDER_HEADERS_ALL95723';                         

l_line_tbl(1).orig_sys_line_ref := 'OE_ORDER_LINES_ALL196892';   

l_line_tbl(1).payment_term_id := 4;                                 

l_line_tbl(1).price_list_id := p_price; 

l_line_tbl(1).UNIT_SELLING_PRICE := 1470;  

l_line_tbl(1).UNIT_LIST_PRICE := 1470;

l_line_tbl(1).calculate_price_flag :=  'Y';

l_line_tbl(1).line_type_id := P_LINE_TYPE_ID;         

l_line_tbl(1).schedule_ship_date := SYSDATE;                                     

l_line_tbl(1).request_date := SYSDATE;

l_line_tbl(1).booked_flag := 'N';

l_line_tbl(1).delivery_lead_time :=0;

l_line_tbl(1).schedule_arrival_date := sysdate;

l_line_tbl(1).schedule_status_code := 'SCHEDULED';

l_line_tbl(1).lock_control  := 2;  

l_line_tbl(1).re_source_flag := 'N';

l_line_tbl(1).visible_demand_flag := 'Y';

l_line_tbl(1).shipping_interfaced_flag := 'N';

l_line_tbl(1).dep_plan_required_flag := null;

l_line_tbl(1).flow_status_code := 'ENTERED';                 

l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;

Oe_Order_Pub.Process_Order 

(p_api_version_number =>  1.0,

   p_init_msg_list =>  Fnd_Api.G_false ,

   p_return_values =>  Fnd_Api.G_false ,

   p_action_commit =>  Fnd_Api.G_false ,

   x_return_status => l_return_status, 

   x_msg_count => l_msg_count, 

   x_msg_data => l_msg_data,   

   p_header_rec =>  l_Header_Rec_Type, 

   p_old_header_rec => l_Header_Rec_Type,

   p_header_val_rec => l_header_val_rec,

   p_old_header_val_rec =>  l_header_val_rec,

   p_Header_Adj_tbl =>  l_Header_Adj_tbl,

   p_old_Header_Adj_tbl =>  l_Header_Adj_tbl,

   p_Header_Adj_val_tbl =>  l_Header_Adj_val_tbl ,

   p_old_Header_Adj_val_tbl =>  l_Header_Adj_val_tbl,

   p_Header_price_Att_tbl =>  l_Header_price_Att_tbl ,

   p_old_Header_Price_Att_tbl => l_Header_price_Att_tbl ,

   p_Header_Adj_Att_tbl =>  l_Header_Adj_Att_tbl,

   p_old_Header_Adj_Att_tbl =>  l_Header_Adj_Att_tbl ,

   p_Header_Adj_Assoc_tbl =>  l_Header_Adj_Assoc_tbl ,

   p_old_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl ,

   p_Header_Scredit_tbl =>  l_Header_Scredit_tbl ,

   p_old_Header_Scredit_tbl =>  l_Header_Scredit_tbl ,

   p_Header_Scredit_val_tbl =>  l_Header_Scredit_val_tbl ,

   p_old_Header_Scredit_val_tbl =>  l_Header_Scredit_val_tbl ,

   p_line_tbl =>  l_line_tbl,

   p_old_line_tbl => l_line_tbl,

   p_line_val_tbl =>  l_line_val_tbl,

   p_old_line_val_tbl => l_line_val_tbl,

   p_Line_Adj_tbl =>  l_Line_Adj_tbl ,

   p_old_Line_Adj_tbl =>  l_Line_Adj_tbl,

   p_Line_Adj_val_tbl =>  l_Line_Adj_val_tbl ,

   p_old_Line_Adj_val_tbl =>  l_Line_Adj_val_tbl,

   p_Line_price_Att_tbl => l_Line_price_Att_tbl,

   p_old_Line_Price_Att_tbl => l_Line_price_Att_tbl ,

   p_Line_Adj_Att_tbl =>  l_Line_Adj_Att_tbl ,

   p_old_Line_Adj_Att_tbl =>  l_Line_Adj_Att_tbl ,

   p_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl ,

   p_old_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl,

   p_Line_Scredit_tbl =>  l_Line_Scredit_tbl,

   p_old_Line_Scredit_tbl =>  l_Line_Scredit_tbl,

   p_Line_Scredit_val_tbl => l_Line_Scredit_val_tbl ,

   p_old_Line_Scredit_val_tbl  => l_Line_Scredit_val_tbl,

   p_Lot_Serial_tbl  => l_Lot_Serial_tbl ,

   p_old_Lot_Serial_tbl =>  l_Lot_Serial_tbl,

   p_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,

   p_old_Lot_Serial_val_tbl => l_Lot_Serial_val_tbl,

   p_action_request_tbl =>  l_action_request_tbl , 

   x_header_rec => x_header_rec,

   x_header_val_rec => x_header_val_rec, 

   x_Header_Adj_tbl => x_Header_Adj_tbl, 

   x_Header_Adj_val_tbl => x_Header_Adj_val_tbl,

   x_Header_price_Att_tbl => x_Header_price_Att_tbl,

   x_Header_Adj_Att_tbl => x_Header_Adj_Att_tbl, 

   x_Header_Adj_Assoc_tbl => x_Header_Adj_Assoc_tbl,

   x_Header_Scredit_tbl => x_Header_Scredit_tbl,  

   x_Header_Scredit_val_tbl => x_Header_Scredit_val_tbl,

   x_line_tbl => x_line_tbl,

   x_line_val_tbl => x_line_val_tbl, 

   x_Line_Adj_tbl => x_Line_Adj_tbl,

   x_Line_Adj_val_tbl => x_Line_Adj_val_tbl,

   x_Line_price_Att_tbl => x_Line_price_Att_tbl,

   x_Line_Adj_Att_tbl => x_Line_Adj_Att_tbl, 

   x_Line_Adj_Assoc_tbl => x_Line_Adj_Assoc_tbl,

   x_Line_Scredit_tbl => x_Line_Scredit_tbl,

   x_Line_Scredit_val_tbl => x_Line_Scredit_val_tbl,

   x_Lot_Serial_tbl => x_Lot_Serial_tbl, 

   x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl, 

   x_action_request_tbl => x_action_request_tbl, 

   p_rtrim_data =>  'N'

   --p_validate_desc_flex => 'Y' 

);

DBMS_OUTPUT.put_line( 'x_return_status : ' || L_return_status);                                                

DBMS_OUTPUT.put_line( 'x_header_id :   ' || x_header_rec.header_id);

DBMS_OUTPUT.put_line( 'x_msg_count :     ' || L_msg_count);

DBMS_OUTPUT.put_line(OE_CODE_CONTROL.Code_Release_Level);

DBMS_OUTPUT.put_line(l_return_status);

IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN                                                                           

FOR i IN 1..l_msg_count

LOOP                                                                                            

oe_msg_pub.get(p_msg_index => i, 

p_encoded => fnd_api.g_false,

p_data => l_msg_data, 

p_msg_index_out => l_msg_index_out);                                               

DBMS_OUTPUT.put_line( l_msg_data);                                                             

END LOOP;                                                                                                           

END IF;                                                                                           

IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN                                                                             

IF l_msg_count > 0 THEN                                                                                                       

FOR i IN 1..l_msg_count

LOOP                                                                                             

oe_msg_pub.get(p_msg_index => i, 

p_encoded => fnd_api.g_false,

p_data => l_msg_data, 

p_msg_index_out => l_msg_index_out);                                               

DBMS_OUTPUT.put_line( l_msg_data);                                                           

END LOOP;                                                                             

END IF;                                                                              

DBMS_OUTPUT.put_line( 'PROCEDURE COMPLETED SUCCESFULLY ...');

COMMIT;                                                                                                

END IF;                                

DBMS_OUTPUT.put_line( 'AFTER COMMITING ORDER API ... '); 

EXCEPTION                                                                                                                                                                                               

WHEN NO_DATA_FOUND THEN                                                                                                                                                                             

DBMS_OUTPUT.put_line( 'EXCEPTION : NO DATA FOUND!');                                                                                                                                

RETURN;                                                                                                                                                                                          

WHEN TOO_MANY_ROWS THEN                                                                                                                                                                             

DBMS_OUTPUT.put_line( 'EXCEPTION : TOO MANY ROWS!');                                                                                                                             

RETURN;                                                                                                                                                                                              

WHEN OTHERS THEN                                                                                                                                                                                    

DBMS_OUTPUT.put_line( 'EXCEPTION : ERROR!'||SQLERRM||SQLCODE);                                                                                                                                     

RETURN;        

end  xx_OM_PROC ;

/

 Receipt APPLY

In accounts receivable we can create apply receipt using the API as follows:

Declare

AR_RECEIPT_API_PUB.create_and_apply

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_TRUE,

p_validation_level => FND_API.G_VALID_LEVEL_FULL,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data,

p_amount => p_amount,

p_receipt_number => 'p_receipt_number',

p_receipt_date => 'p_receipt_date',

p_gl_date => 'p_gl_date',

p_customer_number => 'p_customer_number',

p_location => 'p_location_value',

p_receipt_method_id => p_receipt_method_id,

p_trx_number => 'p_trx_number',

p_cr_id => l_cash_receipt_id );

dbms_output.put_line('Message count ' || l_msg_count);

dbms_output.put_line('Cash Receipt ID ' || l_cash_receipt_id );

IF l_msg_count = 1 Then

dbms_output.put_line('l_msg_data '||l_msg_data);

ELSIF l_msg_count > 1 Then

LOOP

p_count := p_count+1;

l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);

if l_msg_data is NULL Then

EXIT;

END IF;

dbms_output.put_line('Message' || p_count ||' ---'||l_msg_data);

END LOOP;

END IF;

Unapply AR trascations

 Oracle Application unapply credit memo

As discussed in my preovious post, credit memo is issued to nullify pending receivbale.If the payment already applied to the transaction then we first make sure transaction is unapplied and then only apply credit memo in those transactions

The process to unapply credit memo can be done using api:

AR_RECEIPT_API_PUB.unapply

                                                                ( p_api_version => 1.0,

                                                                p_init_msg_list => FND_API.G_TRUE,

                                                                p_commit => FND_API.G_TRUE,

                                                                p_validation_level => FND_API.G_VALID_LEVEL_FULL,

                                                                x_return_status => l_return_status,

                                                                x_msg_count => v_msg_count,

                                                                x_msg_data => v_msg_data,

                                                                p_cash_receipt_id => LC_REC.cash_receipt_id,

                                                                p_customer_trx_id=>I_LATE_FEES.customer_trx_id,

                                                                p_reversal_gl_date => sysdate

                                                            

                                                                );

Oracle Application Credit Memo R12

 Oracle Application  R12 credit memo:

Credit memo is documents which is issued by seller to buyer against return or exchange, billing error or price adjustment.

In oracle application, when seller issues credit memo against the invoices of the buyer, the liablity of buyer to pay bills is removed.

We can create credit memo in front end  by going to AR->Cash Receipt as credit memo.
We need to make sure the right amount , buyer name and billing address so that credit memo gets applied to wrigth place.

Technically we can apply credit memo by using following oracle supplied credit memo.

The following program is for demo purpose only.The program fetches the late fees, check if payment made withinn xxx days .If payment made then it applies credit memo for those late fees invoices.

declare 

v_return_status varchar2 (1);

p_count number;

v_msg_count number;

v_msg_data varchar2 (2000);

v_request_id number;

 

l_cm_lines_tbl arw_cmreq_cover.cm_line_tbl_type_cover;

l_customer_trx_id number;

cm_trx_id number;

v_interface_header_rec arw_cmreq_cover.pq_interface_rec_type;

ind number;

l_trx_number varchar2 (30):= 0;

l_org_id number:= 0;

 

v_trx_date date;

l_payment_flag boolean;

/*the cursor fetches all of the late fees type invoice*/

cursor c_fetch_late_fees ( p_org_id number default null, p_cust_name varchar2 default null, p_sales_order_no varchar2 default null, p_trx_number varchar default null ) is 

select

   rct.customer_trx_id,

   rct.trx_number invoice_number,

   rct.recurred_from_trx_number,

   rct.reason_code,

   arps.amount_due_original balance_original,

   arps.amount_due_remaining balance_due_remaining,

   hp.party_name bill_to_customer,

   case

      rctt.type 

      when

         'cb' 

      then

         'chargeback' 

      when

         'cm' 

      then

         'credit memo' 

      when

         'dm' 

      then

         'debit memo' 

      when

         'dep'

      then

         'deposit' 

      when

         'guar'

      then

         'guarantee' 

      when

         'inv' 

      then

         'invoice' 

      when

         'pmt' 

      then

         'receipt' 

      else

         'invoice' 

   end

   invoice_class, rct.invoice_currency_code currency, rct.trx_date inv_date, rctd.gl_date gl_date, 

   (

      select

         name 

      from

         apps.ra_terms rat 

      where

         rat.term_id = rct.term_id 

   )

   terms,

   rctt.name order_type,

   rct.attribute15 installment_no,

   rct.attribute6,

   rct.attribute7,

   rct.attribute8,

   rct.attribute9,

   rct.attribute14,

   rct.attribute15,

   rct.ct_reference,

   owtt.product_type 

from

   ra_customer_trx_all rct,

   ra_customer_trx_lines_all rctl,

   ra_cust_trx_line_gl_dist_all rctd,

   hz_parties hp,

   hz_cust_accounts_all hca,

   ra_cust_trx_types_all rctt,

   hr_operating_units hou,

   ar_payment_schedules_all arps 

where

   rct.customer_trx_id = rctd.customer_trx_id 

   and rct.customer_trx_id = rctl.customer_trx_id 

   and rctl.customer_trx_line_id = rctd.customer_trx_line_id 

   and rct.bill_to_customer_id = hca.cust_account_id 

   and arps.status = 'op' 

   and arps.class = 'inv' 

   and okch.bill_to_site_use_id = rct.bill_to_site_use_id 

   and hp.party_id = hca.party_id 

   and rct.cust_trx_type_id = rctt.cust_trx_type_id 

   and rct.org_id = rctt.org_id 

   and rct.org_id = hou.organization_id 

   and arps.customer_trx_id( + ) = rct.customer_trx_id 

   and rct.org_id = nvl (p_org_id, rct.org_id) 

   and rct.trx_number = nvl (p_trx_number, rct.trx_number) 

   and hp.party_name = nvl (p_cust_name, hp.party_name) 

   and nvl (rct.ct_reference, 'xx') = nvl (nvl (p_sales_order_no, rct.ct_reference), 'xx') 

group by

   rct.trx_number,

   arps.amount_due_original,

   arps.amount_due_remaining,

   hp.party_name,

   rctt.type,

   rct.invoice_currency_code,

   rct.trx_date,

   rctd.gl_date,

   rct.term_id,

   rctt.name,

   rct.attribute6,

   rct.attribute7,

   rct.attribute8,

   rct.attribute9,

   rct.attribute14,

   rct.attribute15,

   rct.ct_reference,

   rct.reason_code,

   rct.recurred_from_trx_number,

   rct.customer_trx_id,

   recurred_from_trx_number,

   product_type 

order by

   inv_date desc ;

/*the cursor fetches all of the invoices, for which late fees has been generated */

cursor c_fetch_related_invoice (p_org_id varchar2, p_trx_date date, p_ct_reference varchar2) is 

select

   trx_number,

   trx_date,

   interface_header_attribute9,

   ct_reference,

   amount_due_remaining,

   set_of_books_id 

from

   (

      select

         rct.trx_number,

         rct.cust_trx_type_id,

         rct.trx_date,

         rct.set_of_books_id,

         rct.batch_id,

         rct.batch_source_id,

         rct.bill_to_customer_id,

         rct.bill_to_site_use_id,

         rct.term_id,

         rct.invoice_currency_code,

         rct.attribute6,

         rct.attribute7,

         rct.attribute8,

         rct.attribute9,

         rct.attribute11,

         rct.attribute14,

         rct.attribute15,

         rct.interface_header_attribute6,

         rct.interface_header_attribute7,

         rct.interface_header_context,

         rct.interface_header_attribute14,

         rct.interface_header_attribute9,

         rct.status_trx,

         rct.org_id,

         rctl.amount_due_original,

         rctl.amount_due_remaining,

         rctl.amount_includes_tax_flag,

         rct.ct_reference 

      from

         ra_customer_trx_all rct,

         ra_customer_trx_lines_all rctl,

         ar_payment_schedules_all aprs 

      where

         rct.customer_trx_id = rctl.customer_trx_id 

         and rct.customer_trx_id = aprs.customer_trx_id 

         and rct.trx_date = p_trx_date 

         and rct.batch_source_id = xx 

         and rct.cust_trx_type_id = xx 

         and rct.invoice_currency_code = xx 

         and aprs.class = 'inv' 

      order by

         trx_date desc

   )

order by

   trx_date desc ;

/*the cursor fetches if invoice has been paid or not */

cursor check_invoice_paid_yn (p_trx_number varchar2, p_ct_refernce varchar2) is 

select

   rct.trx_number invoice_no,

   rct.ct_reference 

from

   ar_receivable_applications_all ara,

   ar_cash_receipts_all acr,

   ra_customer_trx_all rct 

where

   ara.status = 'app' 

   and ara.cash_receipt_id = acr.cash_receipt_id 

   and ara.applied_customer_trx_id = rct.customer_trx_id 

   and rct.trx_number in p_trx_number 

   and rct.ct_reference = p_ct_refernce ;

lc check_invoice_paid_yn % rowtype;

cursor c_inv (p_trx_number varchar2, p_ct_refernce varchar2 ) is 

select

   rct.trx_number,

   rct.customer_trx_id,

   rctl.customer_trx_line_id,

   rctl.quantity_invoiced,

   unit_selling_price 

from

   ra_customer_trx_all rct,

   ra_customer_trx_lines_all rctl 

where

   rct.customer_trx_id = rctl.customer_trx_id 

   and trx_number = p_trx_number 

   and ct_reference = p_ct_refernce 

   and line_type = 'line';

begin

   for i_late_fees in c_fetch_late_fees loop 

   begin

      if i_late_fees.product_type = 'xx' 

   then

      mo_global.set_policy_context ('s', xx);

l_org_id:= xx;

elsif i_late_fees.product_type in 

(

   'xx', 'xx'

)

then

   mo_global.set_policy_context ('s', xx);

l_org_id:= xx;

   end

   if;

exception 

when

   others 

then

   dbms_output.put_line('error setting org' || sqlerrm || sqlcode);

end

;

select

   to_date(i_late_fees.inv_date) - 5 into v_trx_date 

from

   dual;

for l_related_invoice in c_fetch_related_invoice (l_org_id , v_trx_date , i_late_fees.ct_reference ) loop l_payment_flag:= false;

open check_invoice_paid_yn (p_trx_number => l_related_invoice.trx_number, p_ct_refernce => l_related_invoice.ct_reference );

fetch check_invoice_paid_yn into lc;

if check_invoice_paid_yn % found 

then

   l_payment_flag:= true;

elsif check_invoice_paid_yn % notfound 

then

   l_payment_flag:= false;

end

if;

close check_invoice_paid_yn;

if l_payment_flag = true 

then

   for lc_inv in c_inv (l_trx_number, i_late_fees.ct_reference) loop dbms_output.put_line ('invoking credit memo creation process for trx_no and  ct_refernce are ' || l_trx_number || '  and   ' || i_late_fees.ct_reference);

ind := 1;

l_customer_trx_id := lc_inv.customer_trx_id;

l_cm_lines_tbl (ind).customer_trx_line_id := lc_inv.customer_trx_line_id;

l_cm_lines_tbl (ind).quantity_credited := lc_inv.quantity_invoiced * - 1;

l_cm_lines_tbl (ind).price := lc_inv.unit_selling_price;

l_cm_lines_tbl (ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * - 1;

end

loop;

ar_credit_memo_api_pub.create_request ( -- standard api parameters

p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false-- credit memo request parameters

, p_customer_trx_id => l_customer_trx_id, p_line_credit_flag => 'y', p_cm_line_tbl => l_cm_lines_tbl, p_cm_reason_code => 'return', p_skip_workflow_flag => 'y', p_batch_source_name => 'xx', p_interface_attribute_rec => v_interface_header_rec, p_credit_method_installments => null, p_credit_method_rules => null, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, x_request_id => v_request_id );

* / commit;

dbms_output.put_line ('message count ' || v_msg_count);

if v_msg_count = 1 

then

   dbms_output.put_line ('l_msg_data ' || v_msg_data);

elsif v_msg_count > 1 

then

   loop p_count := p_count + 1;

v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

if v_msg_data is null 

then

   exit;

end

if;

dbms_output.put_line ('message' || p_count || ' ---' || v_msg_data);

end

loop;

end

if;

if v_return_status <> 's' 

then

   dbms_output.put_line ('failed');

else

   select

      cm_customer_trx_id into cm_trx_id 

   from

      ra_cm_requests_all 

   where

      request_id = v_request_id;

dbms_output.put_line (' cm trx_id = ' || cm_trx_id);

-- you can issue a commit; at this point if you want to save the created credit memo to the database

-- commit;

end

if;

end

if;

end

loop;

end

loop;

end

;