[Home] [Help]
559: AND Paydist1.invoice_distribution_id = g_xface_rec.invoice_distribution_id
560: and Paydist2.invoice_distribution_id = g_xface_rec.invoice_distribution_id
561: AND ( paydist1.pa_addition_flag = 'Y' OR --interfaced payments
562: PayDIST1.invoice_payment_id IN ( SELECT xface.cdl_system_reference4 -- Payments marked for interface in current run
563: FROM pa_transaction_interface_all xface
564: WHERE xface.transaction_source = G_txn_source
565: and xface.cdl_system_reference2 = g_xface_rec.invoice_id
566: and xface.cdl_system_reference5 = g_xface_rec.invoice_distribution_id
567: and xface.cdl_system_reference4 is not NULL
999: l_adj_ei_populated := 'Y' ;
1000: END IF;
1001:
1002: --REL12 : AP lines uptake enhancement :
1003: --Loop for all pa_transaction_interface_all records having same invoice_id,
1004: --invoice distribution id and Invoice payment id
1005:
1006: FOR i in t_txn_interface_id.FIRST..t_txn_interface_id.LAST LOOP --REL12 :Ap lines Uptake enhancement
1007:
2423: v_cdl_line_num tab_line_num;
2424:
2425: cursor get_xface_exp is
2426: select gbc.rowid, txn.expenditure_item_id
2427: from pa_transaction_interface_all txn, gms_bc_packets gbc
2428: where gbc.request_id = p_request_id
2429: and txn.txn_interface_id = gbc.document_header_id
2430: and nvl(txn.transaction_status_code, 'Z') <> 'R'
2431: and gbc.status_code = 'P'
2592: end if;
2593:
2594: elsif (p_process = 'Interface') then
2595:
2596: -- update expenditure_item_id from pa_transaction_interface to
2597: -- gms_bc_packets' document_header_id.
2598: -- re-sequenced code for bug :3690812
2599:
2600: open get_xface_exp;
2754: p_sys_ref5 VARCHAR2,
2755: p_sys_ref4 VARCHAR2,
2756: p_interface_id NUMBER ) IS
2757: select xface.acct_raw_cost
2758: FROM pa_transaction_interface xface
2759: WHERE xface.transaction_source = G_txn_source
2760: and xface.cdl_system_reference2 = p_sys_ref2
2761: and xface.cdl_system_reference5 = p_sys_ref5
2762: and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
3164: where request_id = p_request_id
3165: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3166: and status_code = 'P';
3167:
3168: -- Get all rejected transactions from pa_transaction_interface.
3169: -- Use this to fail gms_bc_packets records.
3170:
3171: cursor get_failed_txns is
3172: select distinct gbp.packet_id,
3170:
3171: cursor get_failed_txns is
3172: select distinct gbp.packet_id,
3173: xface.transaction_rejection_code
3174: from pa_transaction_interface_all xface,
3175: gms_bc_packets gbp
3176: where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
3177: and gbp.request_id = p_request_id
3178: and gbp.parent_bc_packet_id is null
3186: v_all_pkts_failed varchar2(1);
3187:
3188: v_packet_id tt_bc_packet_id;
3189: type tt_reject_code is table of
3190: pa_transaction_interface.transaction_rejection_code%TYPE;
3191: v_reject_code tt_reject_code;
3192: v_status varchar2(1);
3193:
3194: Begin
3278: TO_CHAR(adl.invoice_id)||'|'||TO_CHAR(adl.invoice_distribution_id) IN
3279: (SELECT
3280: (trx.cdl_system_reference2)||'|'||TO_CHAR(trx.cdl_system_reference5)
3281: FROM gms_bc_packets pkt,
3282: pa_transaction_interface_all trx
3283: WHERE pkt.request_id=p_request_id
3284: AND pkt.txn_interface_id=trx.txn_interface_id
3285: AND substr(nvl(pkt.result_code,'P65'), 1, 1)='P'
3286: AND pkt.status_code='P'
3290: AND adl.document_type='AP'
3291: AND adl.adl_status='A';
3292:
3293: -- update the status of gms_bc_packets. update expenditure_item_id
3294: -- from pa_transaction_interface to gms_bc_packets.
3295:
3296: if v_all_pkts_failed = 'N' then
3297:
3298: g_error_stage := 'TieBack_Xface: Calling Create ADLs';
3424: v_userid,
3425: v_userid,
3426: v_date,
3427: v_login
3428: from pa_transaction_interface_all txn,
3429: pa_expenditure_items_all ei,
3430: pa_cost_distribution_lines_all cdl,
3431: ap_invoice_distributions_all apd,
3432: gms_award_distributions adl
3454:
3455:
3456: -------------------------------------------------------------------------------
3457: -- Procedure marks the current packet's interface data as failed.
3458: -- Updates gms_bc_packets and pa_transaction_interface tables.
3459: -------------------------------------------------------------------------------
3460:
3461: Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER,
3462: p_status OUT NOCOPY VARCHAR2) is
3482:
3483: if c1%FOUND then
3484: p_status := 'F';
3485:
3486: update pa_transaction_interface
3487: set transaction_rejection_code = 'GMS_FC_ERROR',
3488: transaction_status_code = 'R'
3489: where txn_interface_id = v_txn_interface_id;
3490: