[Home] [Help]
95: X_ei_id NUMBER(15) DEFAULT NULL;
96:
97: G_CDL_SYSTEM_REFERENCE2 NUMBER;
98: G_CDL_SYSTEM_REFERENCE3 NUMBER;
99: G_CDL_SYSTEM_REFERENCE4 pa_transaction_interface_all.cdl_system_reference4%TYPE; --2339216-apdisc; Commented for bug 4281765
100: /* REL12-AP Lines uptake
101: ** Support for cdl_system_reference5
102: ** cdl_system_reference5 to store invoice distribution ID
103: */
110: DEFAULT NULL;
111: G_post_processing_extn pa_transaction_sources.post_processing_extension%TYPE
112: DEFAULT NULL;
113:
114: G_reversed_orig_txn_reference PA_TRANSACTION_INTERFACE_ALL.reversed_orig_txn_reference%TYPE;
115:
116: /* Bug 4107560 */
117: G_prev_ORIG_TRAN_REF PA_TRANSACTION_INTERFACE_ALL.ORIG_TRANSACTION_REFERENCE%TYPE;
118: G_WIP_RESOURCE_ID PA_TRANSACTION_INTERFACE_ALL.WIP_RESOURCE_ID%TYPE;
113:
114: G_reversed_orig_txn_reference PA_TRANSACTION_INTERFACE_ALL.reversed_orig_txn_reference%TYPE;
115:
116: /* Bug 4107560 */
117: G_prev_ORIG_TRAN_REF PA_TRANSACTION_INTERFACE_ALL.ORIG_TRANSACTION_REFERENCE%TYPE;
118: G_WIP_RESOURCE_ID PA_TRANSACTION_INTERFACE_ALL.WIP_RESOURCE_ID%TYPE;
119:
120:
121: G_project_type_class PA_PROJECT_TYPES_ALL.PROJECT_TYPE_CLASS_CODE%TYPE;
114: G_reversed_orig_txn_reference PA_TRANSACTION_INTERFACE_ALL.reversed_orig_txn_reference%TYPE;
115:
116: /* Bug 4107560 */
117: G_prev_ORIG_TRAN_REF PA_TRANSACTION_INTERFACE_ALL.ORIG_TRANSACTION_REFERENCE%TYPE;
118: G_WIP_RESOURCE_ID PA_TRANSACTION_INTERFACE_ALL.WIP_RESOURCE_ID%TYPE;
119:
120:
121: G_project_type_class PA_PROJECT_TYPES_ALL.PROJECT_TYPE_CLASS_CODE%TYPE;
122: /* added for the bug# 1428216, starts here */
2502: --
2503: -- Bug:5502147 R12.PJ:XB9:QA:APL: ISSUES IN EI DATE VALIDATION IN PROJECTS
2504: -- Following is done to support Query by project number or task number in the transaction review form.
2505: --
2506: update pa_transaction_interface_all
2507: Set project_number = ( select segment1
2508: from pa_projects_all
2509: where project_id = P_project_id ),
2510: task_number = ( select task_number from pa_tasks
5582: v_dst_ifIDTab,
5583: v_dst_TxnifIDTab,
5584: v_dst_eidateTab,
5585: v_dst_txnstatcdTab
5586: from pa_transaction_interface_all a,
5587: pa_transaction_interface_all b
5588: where a.interface_id = X_xface_id
5589: and a.transaction_source = X_transaction_source
5590: and a.batch_name = X_batch
5583: v_dst_TxnifIDTab,
5584: v_dst_eidateTab,
5585: v_dst_txnstatcdTab
5586: from pa_transaction_interface_all a,
5587: pa_transaction_interface_all b
5588: where a.interface_id = X_xface_id
5589: and a.transaction_source = X_transaction_source
5590: and a.batch_name = X_batch
5591: and a.transaction_source = b.transaction_source
5608: log_message('log_message: Updating the ei date ' );
5609: END IF ;
5610:
5611: FORALL indx in 1..v_src_TxnIfIDTab.count
5612: Update pa_transaction_interface_all
5613: set expenditure_item_date = v_dst_eidateTab(indx)
5614: where txn_interface_id = v_src_TxnIfIDTab(indx) ;
5615:
5616:
5618: log_message('log_message: Updating the ei date on adjusted transactions(reversals) ' );
5619: END IF ;
5620:
5621: FORALL indx in 1..v_src_TxnIfIDTab.count
5622: Update pa_transaction_interface_all
5623: set expenditure_item_date = v_dst_eidateTab(indx)
5624: where adjusted_txn_interface_id = v_src_TxnIfIDTab(indx)
5625: and net_zero_adjustment_flag = 'Y' ;
5626:
5632: a.cdl_system_reference2
5633: BULK Collect into
5634: v_week_ending_dtTab,
5635: v_doc_headerIDTab
5636: from pa_transaction_interface_all a,
5637: pa_transaction_interface_all b
5638: where a.interface_id = X_xface_id
5639: and a.transaction_source = X_transaction_source
5640: and a.batch_name = X_batch
5633: BULK Collect into
5634: v_week_ending_dtTab,
5635: v_doc_headerIDTab
5636: from pa_transaction_interface_all a,
5637: pa_transaction_interface_all b
5638: where a.interface_id = X_xface_id
5639: and a.transaction_source = X_transaction_source
5640: and a.batch_name = X_batch
5641: and a.transaction_source = b.transaction_source
5654: log_message('log_message: Update the weekending date... '|| v_doc_headerIDTab.count );
5655: END IF ;
5656:
5657: FORALL indx in 1..v_doc_headerIDTab.count
5658: UPDATE pa_transaction_interface_all
5659: set expenditure_ending_date = v_week_ending_dtTab(indx)
5660: where interface_id = X_xface_id
5661: and transaction_source = X_transaction_source
5662: and batch_name = X_batch
5666: IF PG_DEBUG = 'Y' THEN
5667: log_message('log_message: Deleting the previous batch rejected transactions. ' );
5668: END IF ;
5669: FORALL indx in 1..v_dst_TxnIfIdTab.count
5670: delete from pa_transaction_interface_all
5671: where txn_interface_id = v_dst_TxnIfIdTab(indx) ;
5672:
5673: END IF ;
5674: IF PG_DEBUG = 'Y' THEN
5901: , NULL -- sc_xfer_code
5902: , 0 -- final_payment_id
5903: -- , agreement_id --FSIO Changes
5904: -- , agreement_number
5905: FROM pa_transaction_interface
5906: WHERE transaction_source = X_transaction_source
5907: AND batch_name = current_batch
5908: AND transaction_status_code = 'P'
5909: AND system_linkage in ('ST', 'OT')
6060: , sc_xfer_code -- supplier cost transfer status code.
6061: , 0 -- final_payment_id
6062: -- , agreement_id --FSIO Changes
6063: -- , agreement_number
6064: FROM pa_transaction_interface
6065: WHERE transaction_source = X_transaction_source
6066: AND batch_name = current_batch
6067: AND transaction_status_code = 'P'
6068: AND system_linkage = curr_etype_class_code
6694: -- We allow good ap invoice distributions to interface in a expenditure
6695: -- and do not reject all the expenditure items in expenditure
6696: -- This is only done for VI items.
6697:
6698: UPDATE pa_transaction_interface
6699: -- SST Change: If there's post import extension, then
6700: -- set status code to 'I' so the post import extension
6701: -- will pick it up, otherwise set to 'A' meaning it has
6702: -- completed the transfer process. Post-extension will
6722: IF PG_DEBUG = 'Y' THEN
6723: pa_debug.G_err_stage := 'Updating txn interface for rejected expenditures';
6724: log_message('log_message: ' || pa_debug.G_err_Stage);
6725: END IF;
6726: UPDATE pa_transaction_interface
6727: SET
6728: transaction_status_code = 'R' ,
6729: expenditure_item_id = NULL,
6730: -- SST Change: For rejected expenditure, we need
6940: where transaction_id = NVL(l_SysRef4Tab(p_indx),0) ;
6941:
6942: cursor c_status_cd is
6943: select transaction_rejection_code
6944: from pa_transaction_interface_all
6945: where txn_interface_id = l_rejected_txn_id
6946: and transaction_status_code = 'R' ;
6947: BEGIN
6948: l_status := p_status ;
8195: Results: For 20,000 records - gain of 30 sec.
8196: Implementing this combined update has been phased out until there is a dire need to do it.
8197: Placing this comment here for reference.
8198: */
8199: UPDATE pa_transaction_interface
8200: SET
8201: transaction_rejection_code = X_status
8202: , interface_id = X_xface_id
8203: , expenditure_id = G_expenditure_id
8257: ' accepted by validateitem';
8258: log_message('log_message: ' || pa_debug.G_err_stage);
8259: END IF;
8260:
8261: UPDATE pa_transaction_interface
8262: SET
8263: transaction_rejection_code = NULL
8264: , interface_id = X_xface_id
8265: , expenditure_id = G_expenditure_id
8633:
8634: Begin
8635: Select WIP_RESOURCE_ID
8636: INTO G_WIP_RESOURCE_ID
8637: FROM PA_TRANSACTION_INTERFACE_ALL
8638: WHERE ORIG_TRANSACTION_REFERENCE = l_OrigTxnRefTab(j)
8639: and SYSTEM_LINKAGE <> 'BTC'
8640: and rownum = 1;
8641: Exception
9524:
9525: SELECT COUNT(DECODE(transaction_status_code, v_sucess, 1, NULL)),
9526: COUNT(DECODE(transaction_status_code, v_failed, 1, NULL))
9527: INTO P_sucess_counter, P_failed_counter
9528: FROM pa_transaction_interface
9529: WHERE interface_id = P_xface_id
9530: AND transaction_status_code IN (v_sucess, v_failed);
9531:
9532: EXCEPTION
9571: a.project_id,
9572: a.budget_line_id ,
9573: a.budget_version_id
9574: from pa_bc_packets a,
9575: pa_transaction_interface b
9576: where a.txn_interface_id = b.txn_interface_id
9577: and a.document_header_id = b.cdl_system_reference2
9578: and a.document_distribution_id = decode(p_calling_mode, 'AP VARIANCE',b.cdl_system_reference5
9579: , 'AP ERV',b.cdl_system_reference5
9596: a.project_id,
9597: a.budget_line_id ,
9598: a.budget_version_id
9599: from pa_bc_packets a,
9600: pa_transaction_interface b,-- moac changes
9601: pa_expenditure_items c
9602: where a.txn_interface_id = b.txn_interface_id
9603: and a.document_header_id = b.expenditure_item_id
9604: and a.document_distribution_id = 1
9623: a.project_id,
9624: a.budget_line_id ,
9625: a.budget_version_id
9626: from pa_bc_packets a,
9627: pa_transaction_interface b -- moac changes
9628: where a.txn_interface_id = b.txn_interface_id
9629: and a.document_header_id = b.cdl_system_reference2
9630: and a.document_distribution_id = b.cdl_system_reference5
9631: and a.document_type = 'AP'
9643: a.project_id,
9644: a.budget_line_id ,
9645: a.budget_version_id
9646: from pa_bc_packets a,
9647: pa_transaction_interface b, -- moac changes
9648: ap_invoice_distributions c,
9649: po_distributions d
9650: where a.txn_interface_id = b.txn_interface_id
9651: and a.document_header_id = d.po_header_id
9662:
9663: CURSOR c_exp_item_id IS
9664: SELECT txn.expenditure_item_id ,
9665: pkt.bc_packet_id
9666: FROM pa_transaction_interface txn,-- moac changes
9667: pa_bc_packets pkt
9668: WHERE pkt.txn_interface_id = txn.txn_interface_id
9669: and pkt.request_id = g_request_id
9670: and txn.transaction_status_code = 'A'
10086: where request_id = g_request_id
10087: and status_code = 'P'
10088: and txn_interface_id in
10089: (select txn_interface_id /*REL12 : AP Lines Uptake changes*/
10090: from pa_transaction_interface_all
10091: where transaction_source = p_calling_mode
10092: and transaction_status_code = 'R'
10093: and orig_transaction_reference = to_char(g_request_id)
10094: );
10105: (select po_header_id, po_distribution_id
10106: from po_distributions_all
10107: where po_distribution_id in
10108: (select po_distribution_id
10109: from ap_invoice_distributions_all inv, pa_transaction_interface_all pti
10110: where inv.invoice_id = pti.cdl_system_reference2
10111: and inv.invoice_distribution_id = pti.cdl_system_reference5 /*REL12 : AP Lines Uptake changes*/
10112: and pti.transaction_source = p_calling_mode
10113: and pti.transaction_status_code = 'R'
10767:
10768: /* Bug 5731450 : Added the cursor to get the exchange rate for the actuals. */
10769: cursor c_acct_exchange_rate is
10770: select acct_exchange_rate
10771: from pa_transaction_interface_all
10772: where txn_interface_id = p_txn_interface_id;
10773:
10774: Procedure GetCommSummAmt(p_sys_ref2 In Number,
10775: p_sys_ref3 In Number,
11912: RAISE;
11913: END ap_cash_based_funds_check;
11914:
11915: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
11916: -- pa_transaction_interface_all table and to derive variables which drives the
11917: -- relieving/reserving of commitments and actuals flow during interface.
11918:
11919: PROCEDURE insert_ap_bc_packets(p_packet_id IN NUMBER,
11920: p_sys_ref2 IN NUMBER,
11926: p_txn_source IN VARCHAR2,
11927: p_adj_exp_item_id IN NUMBER,
11928: p_txn_interface_id IN NUMBER) IS
11929:
11930: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
11931: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
11932:
11933:
11934: CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
11950: ,txn.txn_interface_id --REL12
11951: From Pa_Budget_Versions bv,
11952: Pa_Budget_Types bt,
11953: pa_budgetary_control_options pbct,
11954: pa_transaction_interface_all txn
11955: Where bv.budget_type_code = bt.budget_type_code
11956: and bt.budget_amount_code = 'C'
11957: and bv.current_flag = 'Y'
11958: AND pbct.project_id = bv.project_id
11963: pbct.EXTERNAL_BUDGET_CODE is NULL
11964: )
11965: AND bv.project_id = txn.project_id
11966: AND txn.txn_interface_id in ( SELECT txn1.txn_interface_id
11967: FROM pa_transaction_interface_all txn1
11968: WHERE txn1.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
11969: -- new index usage needs to be verified for below columns
11970: AND txn1.cdl_system_reference2 = p_sys_ref2
11971: AND txn1.cdl_system_reference5 = p_sys_ref5
12032: log_message('log_message: ' || 'In insert_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
12033: log_message('log_message: ' || 'In insert_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
12034: END IF;
12035:
12036: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
12037: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
12038:
12039: IF ( l_adj_act_fc_req = 'Y' OR l_act_fc_req = 'Y' ) THEN
12040:
12114: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
12115: -- reversing/cancelled Payable Invoice getting interfaced to Projects and original
12116: -- interfaced exp in project has been adjusted .
12117: -- In this case insert records into pa_bc_packets to fundscheck actual raw cost which is equal to
12118: -- amount in pa_transaction_interface for each non net zero expenditures associated with this
12119: -- invoice.
12120: -- Note : In this scenraio if non net zero expenditures associated with this invoice are not costed
12121: -- then import process (PAAPIMPB.pls) will reject the transactions with PA_EI_NOT_COST_DISTRIBUTED.
12122: -- Hence the ind_compiled_set_id on original exp's will always be the latest ,so parent_bc_packet_id
12690: END IF; -- p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
12691:
12692:
12693: -- R12 AP lines uptake : Prepayment changes : Below insert will be fired for inserting 'EXP'
12694: -- records by fetching data from pa_transaction_interface_all table for below TXN records :
12695: -- a. Adjusting expenditures in txn_interface table
12696: -- b. For those commitments which were never FC'ed (eg : Prepayment application, pre-payment matched to PO ..)
12697:
12698: IF ((p_adj_act_fc_req = 'Y')
12813: END insert_ap_bc_pkt_autonomous;
12814:
12815:
12816: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
12817: -- pa_transaction_interface_all table and to derive variables which drives the
12818: -- relieving/reserving of commitments and actuals flow during interface.
12819:
12820: PROCEDURE insert_cash_ap_bc_packets(p_packet_id IN NUMBER,
12821: p_sys_ref2 IN NUMBER,
12826: p_adj_exp_item_id IN NUMBER,
12827: p_txn_interface_id IN NUMBER,
12828: p_cash_pay_to_relieve IN NUMBER DEFAULT 0) IS
12829:
12830: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
12831: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
12832:
12833: CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
12834: p_act_fc_req VARCHAR2) IS
12849: ,txn.txn_interface_id --REL12
12850: From Pa_Budget_Versions bv,
12851: Pa_Budget_Types bt,
12852: pa_budgetary_control_options pbct,
12853: pa_transaction_interface_all txn
12854: Where bv.budget_type_code = bt.budget_type_code
12855: and bt.budget_amount_code = 'C'
12856: and bv.current_flag = 'Y'
12857: AND pbct.project_id = bv.project_id
12862: pbct.EXTERNAL_BUDGET_CODE is NULL
12863: )
12864: AND bv.project_id = txn.project_id
12865: AND txn.txn_interface_id in ( SELECT txn1.txn_interface_id
12866: FROM pa_transaction_interface_all txn1
12867: WHERE txn1.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
12868: -- new index usage needs to be verified for below columns
12869: AND txn1.cdl_system_reference2 = p_sys_ref2
12870: AND txn1.cdl_system_reference5 = p_sys_ref5
12931: log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
12932: log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
12933: END IF;
12934:
12935: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
12936: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
12937:
12938: IF ( l_adj_act_fc_req = 'Y' OR l_act_fc_req = 'Y' ) THEN
12939:
13606: END IF; -- p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
13607:
13608:
13609: -- R12 AP lines uptake : Prepayment changes : Below insert will be fired for inserting 'EXP'
13610: -- records by fetching data from pa_transaction_interface_all table for below TXN records :
13611: -- a. Adjusting expenditures in txn_interface table
13612: -- b. For those commitments which were never FC'ed (eg : Prepayment application, pre-payment matched to PO ..)
13613:
13614: IF ((p_adj_act_fc_req = 'Y')
13728: RAISE;
13729: END insert_cash_ap_bc_pkt_auto;
13730:
13731: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
13732: -- pa_transaction_interface_all table and to derive variables which drives the
13733: -- relieving/reserving of commitments and actuals flow during interface.
13734:
13735: PROCEDURE insert_po_bc_packets
13736: (p_packet_id IN NUMBER,
13743: p_txn_source IN VARCHAR2,
13744: p_adj_exp_item_id IN NUMBER,
13745: p_txn_interface_id IN NUMBER) IS
13746:
13747: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
13748: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
13749:
13750: CURSOR c_pa_txn_interface_data IS
13751: Select txn.Project_Id
13765: ,txn.txn_interface_id --REL12
13766: From Pa_Budget_Versions bv,
13767: Pa_Budget_Types bt,
13768: pa_budgetary_control_options pbct,
13769: pa_transaction_interface_all txn
13770: Where bv.budget_type_code = bt.budget_type_code
13771: and bt.budget_amount_code = 'C'
13772: and bv.current_flag = 'Y'
13773: AND pbct.project_id = bv.project_id
13840: log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
13841: log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
13842: END IF;
13843:
13844: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
13845: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
13846:
13847: IF ( l_adj_act_fc_req = 'Y' OR l_act_fc_req = 'Y' ) THEN
13848: