[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 */
1579: , p_fc_document_type IN varchar2 default NULL
1580: -- , p_agreement_id IN OUT NOCOPY NUMBER --FSIO Changes
1581: -- , p_agreement_number IN OUT NOCOPY varchar2)
1582: -- 10253400: Payroll integration enhancement for 12.2..start
1583: ,P_JOB_ID PA_TRANSACTION_INTERFACE_ALL.JOB_ID%TYPE DEFAULT NULL
1584: ,P_JOB_NAME PA_TRANSACTION_INTERFACE_ALL.JOB_NAME%TYPE DEFAULT NULL
1585: ,P_LOCATION_ID PA_TRANSACTION_INTERFACE_ALL.LOCATION_ID%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1580: -- , p_agreement_id IN OUT NOCOPY NUMBER --FSIO Changes
1581: -- , p_agreement_number IN OUT NOCOPY varchar2)
1582: -- 10253400: Payroll integration enhancement for 12.2..start
1583: ,P_JOB_ID PA_TRANSACTION_INTERFACE_ALL.JOB_ID%TYPE DEFAULT NULL
1584: ,P_JOB_NAME PA_TRANSACTION_INTERFACE_ALL.JOB_NAME%TYPE DEFAULT NULL
1585: ,P_LOCATION_ID PA_TRANSACTION_INTERFACE_ALL.LOCATION_ID%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1581: -- , p_agreement_number IN OUT NOCOPY varchar2)
1582: -- 10253400: Payroll integration enhancement for 12.2..start
1583: ,P_JOB_ID PA_TRANSACTION_INTERFACE_ALL.JOB_ID%TYPE DEFAULT NULL
1584: ,P_JOB_NAME PA_TRANSACTION_INTERFACE_ALL.JOB_NAME%TYPE DEFAULT NULL
1585: ,P_LOCATION_ID PA_TRANSACTION_INTERFACE_ALL.LOCATION_ID%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1589: -- Start: Added for bug 16220146
1582: -- 10253400: Payroll integration enhancement for 12.2..start
1583: ,P_JOB_ID PA_TRANSACTION_INTERFACE_ALL.JOB_ID%TYPE DEFAULT NULL
1584: ,P_JOB_NAME PA_TRANSACTION_INTERFACE_ALL.JOB_NAME%TYPE DEFAULT NULL
1585: ,P_LOCATION_ID PA_TRANSACTION_INTERFACE_ALL.LOCATION_ID%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1589: -- Start: Added for bug 16220146
1590: ,P_CBS_ELEMENT_ID PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_ID%TYPE DEFAULT NULL
1583: ,P_JOB_ID PA_TRANSACTION_INTERFACE_ALL.JOB_ID%TYPE DEFAULT NULL
1584: ,P_JOB_NAME PA_TRANSACTION_INTERFACE_ALL.JOB_NAME%TYPE DEFAULT NULL
1585: ,P_LOCATION_ID PA_TRANSACTION_INTERFACE_ALL.LOCATION_ID%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1589: -- Start: Added for bug 16220146
1590: ,P_CBS_ELEMENT_ID PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_ID%TYPE DEFAULT NULL
1591: ,P_CBS_ELEMENT_NAME PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_NAME%TYPE DEFAULT NULL
1586: ,P_LOCATION_CODE PA_TRANSACTION_INTERFACE_ALL.LOCATION_CODE%TYPE DEFAULT NULL
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1589: -- Start: Added for bug 16220146
1590: ,P_CBS_ELEMENT_ID PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_ID%TYPE DEFAULT NULL
1591: ,P_CBS_ELEMENT_NAME PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_NAME%TYPE DEFAULT NULL
1592: -- End: Added for bug 16220146
1593: )
1594: IS
1587: ,P_PAY_ELEMENT_TYPE_ID PA_TRANSACTION_INTERFACE_ALL.PAY_ELEMENT_type_ID%TYPE DEFAULT NULL
1588: -- 10253400: Payroll integration enhancement for 12.2..end
1589: -- Start: Added for bug 16220146
1590: ,P_CBS_ELEMENT_ID PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_ID%TYPE DEFAULT NULL
1591: ,P_CBS_ELEMENT_NAME PA_TRANSACTION_INTERFACE_ALL.CBS_ELEMENT_NAME%TYPE DEFAULT NULL
1592: -- End: Added for bug 16220146
1593: )
1594: IS
1595: temp_status VARCHAR2(30) DEFAULT NULL;
2765: --
2766: -- Bug:5502147 R12.PJ:XB9:QA:APL: ISSUES IN EI DATE VALIDATION IN PROJECTS
2767: -- Following is done to support Query by project number or task number in the transaction review form.
2768: --
2769: update pa_transaction_interface_all
2770: Set project_number = ( select segment1
2771: from pa_projects_all
2772: where project_id = P_project_id ),
2773: task_number = ( select task_number from pa_tasks
6046: v_dst_ifIDTab,
6047: v_dst_TxnifIDTab,
6048: v_dst_eidateTab,
6049: v_dst_txnstatcdTab
6050: from pa_transaction_interface_all a,
6051: pa_transaction_interface_all b
6052: where a.interface_id = X_xface_id
6053: and a.transaction_source = X_transaction_source
6054: and a.batch_name = X_batch
6047: v_dst_TxnifIDTab,
6048: v_dst_eidateTab,
6049: v_dst_txnstatcdTab
6050: from pa_transaction_interface_all a,
6051: pa_transaction_interface_all b
6052: where a.interface_id = X_xface_id
6053: and a.transaction_source = X_transaction_source
6054: and a.batch_name = X_batch
6055: and a.transaction_source = b.transaction_source
6072: log_message('log_message: Updating the ei date ' );
6073: END IF ;
6074:
6075: FORALL indx in 1..v_src_TxnIfIDTab.count
6076: Update pa_transaction_interface_all
6077: set expenditure_item_date = v_dst_eidateTab(indx)
6078: where txn_interface_id = v_src_TxnIfIDTab(indx) ;
6079:
6080:
6082: log_message('log_message: Updating the ei date on adjusted transactions(reversals) ' );
6083: END IF ;
6084:
6085: FORALL indx in 1..v_src_TxnIfIDTab.count
6086: Update pa_transaction_interface_all
6087: set expenditure_item_date = v_dst_eidateTab(indx)
6088: where adjusted_txn_interface_id = v_src_TxnIfIDTab(indx)
6089: and net_zero_adjustment_flag = 'Y' ;
6090:
6096: a.cdl_system_reference2
6097: BULK Collect into
6098: v_week_ending_dtTab,
6099: v_doc_headerIDTab
6100: from pa_transaction_interface_all a,
6101: pa_transaction_interface_all b
6102: where a.interface_id = X_xface_id
6103: and a.transaction_source = X_transaction_source
6104: and a.batch_name = X_batch
6097: BULK Collect into
6098: v_week_ending_dtTab,
6099: v_doc_headerIDTab
6100: from pa_transaction_interface_all a,
6101: pa_transaction_interface_all b
6102: where a.interface_id = X_xface_id
6103: and a.transaction_source = X_transaction_source
6104: and a.batch_name = X_batch
6105: and a.transaction_source = b.transaction_source
6118: log_message('log_message: Update the weekending date... '|| v_doc_headerIDTab.count );
6119: END IF ;
6120:
6121: FORALL indx in 1..v_doc_headerIDTab.count
6122: UPDATE pa_transaction_interface_all
6123: set expenditure_ending_date = v_week_ending_dtTab(indx)
6124: where interface_id = X_xface_id
6125: and transaction_source = X_transaction_source
6126: and batch_name = X_batch
6130: IF PG_DEBUG = 'Y' THEN
6131: log_message('log_message: Deleting the previous batch rejected transactions. ' );
6132: END IF ;
6133: FORALL indx in 1..v_dst_TxnIfIdTab.count
6134: delete from pa_transaction_interface_all
6135: where txn_interface_id = v_dst_TxnIfIdTab(indx) ;
6136:
6137: END IF ;
6138: IF PG_DEBUG = 'Y' THEN
6376: /* Start: Added for bug 16220146 */
6377: , cbs_element_name
6378: , cbs_element_id
6379: /* End: Added for bug 16220146 */
6380: FROM pa_transaction_interface
6381: WHERE transaction_source = X_transaction_source
6382: AND batch_name = current_batch
6383: AND transaction_status_code = 'P'
6384: AND system_linkage in ('ST', 'OT')
6546: /* Start: Added for bug 16220146 */
6547: , cbs_element_name
6548: , cbs_element_id
6549: /* End: Added for bug 16220146 */
6550: FROM pa_transaction_interface
6551: WHERE transaction_source = X_transaction_source
6552: AND batch_name = current_batch
6553: AND transaction_status_code = 'P'
6554: AND system_linkage = curr_etype_class_code
7287: -- We allow good ap invoice distributions to interface in a expenditure
7288: -- and do not reject all the expenditure items in expenditure
7289: -- This is only done for VI items.
7290:
7291: UPDATE pa_transaction_interface
7292: -- SST Change: If there's post import extension, then
7293: -- set status code to 'I' so the post import extension
7294: -- will pick it up, otherwise set to 'A' meaning it has
7295: -- completed the transfer process. Post-extension will
7315: IF PG_DEBUG = 'Y' THEN
7316: pa_debug.G_err_stage := 'Updating txn interface for rejected expenditures';
7317: log_message('log_message: ' || pa_debug.G_err_Stage);
7318: END IF;
7319: UPDATE pa_transaction_interface
7320: SET
7321: transaction_status_code = decode(expenditure_item_id, 0, 'P', 'R') , /* Bug 8709614 */
7322: expenditure_item_id = decode(expenditure_item_id, 0, 0, NULL) /* Bug 8709614 */
7323: -- SST Change: For rejected expenditure, we need
7551: where transaction_id = NVL(l_SysRef4Tab(p_indx),0) ;
7552:
7553: cursor c_status_cd is
7554: select transaction_rejection_code
7555: from pa_transaction_interface_all
7556: where txn_interface_id = l_rejected_txn_id
7557: and transaction_status_code = 'R' ;
7558: BEGIN
7559: l_status := p_status ;
8926: Results: For 20,000 records - gain of 30 sec.
8927: Implementing this combined update has been phased out until there is a dire need to do it.
8928: Placing this comment here for reference.
8929: */
8930: UPDATE pa_transaction_interface
8931: SET
8932: transaction_rejection_code = X_status
8933: , interface_id = X_xface_id
8934: , expenditure_id = G_expenditure_id
8988: ' accepted by validateitem';
8989: log_message('log_message: ' || pa_debug.G_err_stage);
8990: END IF;
8991:
8992: UPDATE pa_transaction_interface
8993: SET
8994: transaction_rejection_code = NULL
8995: , interface_id = X_xface_id
8996: , expenditure_id = G_expenditure_id
9384:
9385: Begin
9386: Select WIP_RESOURCE_ID
9387: INTO G_WIP_RESOURCE_ID
9388: FROM PA_TRANSACTION_INTERFACE_ALL
9389: WHERE ORIG_TRANSACTION_REFERENCE = l_OrigTxnRefTab(j)
9390: and SYSTEM_LINKAGE <> 'BTC'
9391: and rownum = 1;
9392: Exception
10317:
10318: SELECT COUNT(DECODE(transaction_status_code, v_sucess, 1, NULL)),
10319: COUNT(DECODE(transaction_status_code, v_failed, 1, NULL))
10320: INTO P_sucess_counter, P_failed_counter
10321: FROM pa_transaction_interface
10322: WHERE interface_id = P_xface_id
10323: AND transaction_status_code IN (v_sucess, v_failed);
10324:
10325: EXCEPTION
10364: a.project_id,
10365: a.budget_line_id ,
10366: a.budget_version_id
10367: from pa_bc_packets a,
10368: pa_transaction_interface b
10369: where a.txn_interface_id = b.txn_interface_id
10370: and a.document_header_id = b.cdl_system_reference2
10371: and a.document_distribution_id = decode(p_calling_mode, 'AP VARIANCE',b.cdl_system_reference5
10372: , 'AP ERV',b.cdl_system_reference5
10389: a.project_id,
10390: a.budget_line_id ,
10391: a.budget_version_id
10392: from pa_bc_packets a,
10393: pa_transaction_interface b,-- moac changes
10394: pa_expenditure_items c
10395: where a.txn_interface_id = b.txn_interface_id
10396: and a.document_header_id = b.expenditure_item_id
10397: and a.document_distribution_id = 1
10416: a.project_id,
10417: a.budget_line_id ,
10418: a.budget_version_id
10419: from pa_bc_packets a,
10420: pa_transaction_interface b -- moac changes
10421: where a.txn_interface_id = b.txn_interface_id
10422: and a.document_header_id = b.cdl_system_reference2
10423: and a.document_distribution_id = b.cdl_system_reference5
10424: and a.document_type = 'AP'
10436: a.project_id,
10437: a.budget_line_id ,
10438: a.budget_version_id
10439: from pa_bc_packets a,
10440: pa_transaction_interface b, -- moac changes
10441: ap_invoice_distributions c,
10442: po_distributions d
10443: where a.txn_interface_id = b.txn_interface_id
10444: and a.document_header_id = d.po_header_id
10461: a.project_id,
10462: a.budget_line_id ,
10463: a.budget_version_id
10464: from pa_bc_packets a,
10465: pa_transaction_interface b,
10466: ap_self_assessed_tax_dist c,
10467: po_distributions d
10468: where a.txn_interface_id = b.txn_interface_id
10469: and a.document_header_id = d.po_header_id
10480:
10481: CURSOR c_exp_item_id IS
10482: SELECT txn.expenditure_item_id ,
10483: pkt.bc_packet_id
10484: FROM pa_transaction_interface txn,-- moac changes
10485: pa_bc_packets pkt
10486: WHERE pkt.txn_interface_id = txn.txn_interface_id
10487: and pkt.request_id = g_request_id
10488: and txn.transaction_status_code = 'A'
10904: where request_id = g_request_id
10905: and status_code = 'P'
10906: and txn_interface_id in
10907: (select txn_interface_id /*REL12 : AP Lines Uptake changes*/
10908: from pa_transaction_interface_all
10909: where transaction_source = p_calling_mode
10910: and transaction_status_code = 'R'
10911: and orig_transaction_reference = to_char(g_request_id)
10912: );
10923: (select po_header_id, po_distribution_id
10924: from po_distributions_all
10925: where po_distribution_id in
10926: (select po_distribution_id
10927: from ap_invoice_distributions_all inv, pa_transaction_interface_all pti
10928: where inv.invoice_id = pti.cdl_system_reference2
10929: and inv.invoice_distribution_id = pti.cdl_system_reference5 /*REL12 : AP Lines Uptake changes*/
10930: and pti.transaction_source = p_calling_mode
10931: and pti.transaction_status_code = 'R'
11631:
11632: /* Bug 5731450 : Added the cursor to get the exchange rate for the actuals. */
11633: cursor c_acct_exchange_rate is
11634: select acct_exchange_rate
11635: from pa_transaction_interface_all
11636: where txn_interface_id = p_txn_interface_id;
11637:
11638: Procedure GetCommSummAmt(p_sys_ref2 In Number,
11639: p_sys_ref3 In Number,
12810: RAISE;
12811: END ap_cash_based_funds_check;
12812:
12813: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
12814: -- pa_transaction_interface_all table and to derive variables which drives the
12815: -- relieving/reserving of commitments and actuals flow during interface.
12816:
12817: PROCEDURE insert_ap_bc_packets(p_packet_id IN NUMBER,
12818: p_sys_ref2 IN NUMBER,
12824: p_txn_source IN VARCHAR2,
12825: p_adj_exp_item_id IN NUMBER,
12826: p_txn_interface_id IN NUMBER) IS
12827:
12828: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
12829: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
12830:
12831:
12832: CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
12849: ,txn.vendor_id /*bug 13601768*/
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_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
12932: log_message('log_message: ' || 'In insert_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:
13015: -- 4.If p_document type = 'ACT'/'ALL' and p_adj_exp_item_id IS NOT NULL then it implies thats its a
13016: -- reversing/cancelled Payable Invoice getting interfaced to Projects and original
13017: -- interfaced exp in project has been adjusted .
13018: -- In this case insert records into pa_bc_packets to fundscheck actual raw cost which is equal to
13019: -- amount in pa_transaction_interface for each non net zero expenditures associated with this
13020: -- invoice.
13021: -- Note : In this scenraio if non net zero expenditures associated with this invoice are not costed
13022: -- then import process (PAAPIMPB.pls) will reject the transactions with PA_EI_NOT_COST_DISTRIBUTED.
13023: -- Hence the ind_compiled_set_id on original exp's will always be the latest ,so parent_bc_packet_id
13615: END IF; -- p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
13616:
13617:
13618: -- R12 AP lines uptake : Prepayment changes : Below insert will be fired for inserting 'EXP'
13619: -- records by fetching data from pa_transaction_interface_all table for below TXN records :
13620: -- a. Adjusting expenditures in txn_interface table
13621: -- b. For those commitments which were never FC'ed (eg : Prepayment application, pre-payment matched to PO ..)
13622:
13623: IF ((p_adj_act_fc_req = 'Y')
13740: END insert_ap_bc_pkt_autonomous;
13741:
13742:
13743: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
13744: -- pa_transaction_interface_all table and to derive variables which drives the
13745: -- relieving/reserving of commitments and actuals flow during interface.
13746:
13747: PROCEDURE insert_cash_ap_bc_packets(p_packet_id IN NUMBER,
13748: p_sys_ref2 IN NUMBER,
13753: p_adj_exp_item_id IN NUMBER,
13754: p_txn_interface_id IN NUMBER,
13755: p_cash_pay_to_relieve IN NUMBER DEFAULT 0) IS
13756:
13757: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
13758: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
13759:
13760: CURSOR c_pa_txn_interface_data (p_adj_act_fc_req VARCHAR2,
13761: p_act_fc_req VARCHAR2) IS
13777: ,txn.vendor_id
13778: From Pa_Budget_Versions bv,
13779: Pa_Budget_Types bt,
13780: pa_budgetary_control_options pbct,
13781: pa_transaction_interface_all txn
13782: Where bv.budget_type_code = bt.budget_type_code
13783: and bt.budget_amount_code = 'C'
13784: and bv.current_flag = 'Y'
13785: AND pbct.project_id = bv.project_id
13790: pbct.EXTERNAL_BUDGET_CODE is NULL
13791: )
13792: AND bv.project_id = txn.project_id
13793: AND txn.txn_interface_id in ( SELECT txn1.txn_interface_id
13794: FROM pa_transaction_interface_all txn1
13795: WHERE txn1.TRANSACTION_SOURCE = p_txn_source -- Condition for using index
13796: -- new index usage needs to be verified for below columns
13797: AND txn1.cdl_system_reference2 = p_sys_ref2
13798: AND txn1.cdl_system_reference5 = p_sys_ref5
13859: log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
13860: log_message('log_message: ' || 'In insert_cash_ap_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
13861: END IF;
13862:
13863: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
13864: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
13865:
13866: IF ( l_adj_act_fc_req = 'Y' OR l_act_fc_req = 'Y' ) THEN
13867:
14562: END IF; -- p_comm_fc_req ='Y' OR p_act_fc_req = 'Y' THEN
14563:
14564:
14565: -- R12 AP lines uptake : Prepayment changes : Below insert will be fired for inserting 'EXP'
14566: -- records by fetching data from pa_transaction_interface_all table for below TXN records :
14567: -- a. Adjusting expenditures in txn_interface table
14568: -- b. For those commitments which were never FC'ed (eg : Prepayment application, pre-payment matched to PO ..)
14569:
14570: IF ((p_adj_act_fc_req = 'Y')
14686: RAISE;
14687: END insert_cash_ap_bc_pkt_auto;
14688:
14689: -- R12 funds management Uptake : New procedure fired in non-autonomous mode to read
14690: -- pa_transaction_interface_all table and to derive variables which drives the
14691: -- relieving/reserving of commitments and actuals flow during interface.
14692:
14693: PROCEDURE insert_po_bc_packets
14694: (p_packet_id IN NUMBER,
14701: p_txn_source IN VARCHAR2,
14702: p_adj_exp_item_id IN NUMBER,
14703: p_txn_interface_id IN NUMBER) IS
14704:
14705: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
14706: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
14707:
14708: CURSOR c_pa_txn_interface_data IS
14709: Select txn.Project_Id
14724: ,txn.vendor_id
14725: From Pa_Budget_Versions bv,
14726: Pa_Budget_Types bt,
14727: pa_budgetary_control_options pbct,
14728: pa_transaction_interface_all txn
14729: Where bv.budget_type_code = bt.budget_type_code
14730: and bt.budget_amount_code = 'C'
14731: and bv.current_flag = 'Y'
14732: AND pbct.project_id = bv.project_id
14799: log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_adj_act_fc_req '||l_adj_act_fc_req );
14800: log_message('log_message: ' || 'In insert_po_bc_packets - Value of l_act_fc_req '||l_act_fc_req );
14801: END IF;
14802:
14803: -- Cursor to fetch data from pa_transaction_interface_table.This data is later used
14804: -- in autonomous fundscheck insert_ap_bc_pkts_autonomous procedure.
14805:
14806: IF ( l_adj_act_fc_req = 'Y' OR l_act_fc_req = 'Y' ) THEN
14807:
14888:
14889: -- Added for 13966205
14890: CURSOR C_GET_EI_DATE IS
14891: select distinct expenditure_item_date
14892: from pa_transaction_interface_all
14893: where txn_interface_id = p_txn_interface_id;
14894:
14895: Cursor C_po_Commitment Is
14896: Select bcc.Set_Of_Books_Id