[Home] [Help]
544: AND Paydist1.invoice_distribution_id = g_xface_rec.invoice_distribution_id
545: and Paydist2.invoice_distribution_id = g_xface_rec.invoice_distribution_id
546: AND ( paydist1.pa_addition_flag = 'Y' OR --interfaced payments
547: PayDIST1.invoice_payment_id IN ( SELECT xface.cdl_system_reference4 -- Payments marked for interface in current run
548: FROM pa_transaction_interface_all xface
549: WHERE xface.transaction_source = G_txn_source
550: and xface.cdl_system_reference2 = g_xface_rec.invoice_id
551: and xface.cdl_system_reference5 = g_xface_rec.invoice_distribution_id
552: and xface.cdl_system_reference4 is not NULL
968: l_adj_ei_populated := 'Y' ;
969: END IF;
970:
971: --REL12 : AP lines uptake enhancement :
972: --Loop for all pa_transaction_interface_all records having same invoice_id,
973: --invoice distribution id and Invoice payment id
974:
975: FOR i in t_txn_interface_id.FIRST..t_txn_interface_id.LAST LOOP --REL12 :Ap lines Uptake enhancement
976:
2343: v_cdl_line_num tab_line_num;
2344:
2345: cursor get_xface_exp is
2346: select gbc.rowid, txn.expenditure_item_id
2347: from pa_transaction_interface_all txn, gms_bc_packets gbc
2348: where gbc.request_id = p_request_id
2349: and txn.txn_interface_id = gbc.document_header_id
2350: and nvl(txn.transaction_status_code, 'Z') <> 'R'
2351: and gbc.status_code = 'P'
2512: end if;
2513:
2514: elsif (p_process = 'Interface') then
2515:
2516: -- update expenditure_item_id from pa_transaction_interface to
2517: -- gms_bc_packets' document_header_id.
2518: -- re-sequenced code for bug :3690812
2519:
2520: open get_xface_exp;
2674: p_sys_ref5 VARCHAR2,
2675: p_sys_ref4 VARCHAR2,
2676: p_interface_id NUMBER ) IS
2677: select xface.acct_raw_cost
2678: FROM pa_transaction_interface xface
2679: WHERE xface.transaction_source = G_txn_source
2680: and xface.cdl_system_reference2 = p_sys_ref2
2681: and xface.cdl_system_reference5 = p_sys_ref5
2682: and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
3084: where request_id = p_request_id
3085: and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
3086: and status_code = 'P';
3087:
3088: -- Get all rejected transactions from pa_transaction_interface.
3089: -- Use this to fail gms_bc_packets records.
3090:
3091: cursor get_failed_txns is
3092: select distinct gbp.packet_id,
3090:
3091: cursor get_failed_txns is
3092: select distinct gbp.packet_id,
3093: xface.transaction_rejection_code
3094: from pa_transaction_interface_all xface,
3095: gms_bc_packets gbp
3096: where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
3097: and gbp.request_id = p_request_id
3098: and gbp.parent_bc_packet_id is null
3106: v_all_pkts_failed varchar2(1);
3107:
3108: v_packet_id tt_bc_packet_id;
3109: type tt_reject_code is table of
3110: pa_transaction_interface.transaction_rejection_code%TYPE;
3111: v_reject_code tt_reject_code;
3112: v_status varchar2(1);
3113:
3114: Begin
3195: SET adl.payment_status_flag = 'Y'
3196: WHERE (adl.invoice_id ,adl.invoice_distribution_id) IN
3197: (SELECT trx.cdl_system_reference2,trx.cdl_system_reference5
3198: FROM gms_bc_packets pkt,
3199: pa_transaction_interface_all trx
3200: WHERE pkt.request_id = p_request_id
3201: AND pkt.txn_interface_id = trx.txn_interface_id
3202: AND substr(nvl(pkt.result_code, 'P65'), 1, 1) = 'P'
3203: AND pkt.status_code = 'P'
3207: AND adl.document_type ='AP'
3208: AND adl.adl_status = 'A' ;
3209:
3210: -- update the status of gms_bc_packets. update expenditure_item_id
3211: -- from pa_transaction_interface to gms_bc_packets.
3212:
3213: if v_all_pkts_failed = 'N' then
3214:
3215: g_error_stage := 'TieBack_Xface: Calling Create ADLs';
3341: v_userid,
3342: v_userid,
3343: v_date,
3344: v_login
3345: from pa_transaction_interface_all txn,
3346: pa_expenditure_items_all ei,
3347: pa_cost_distribution_lines_all cdl,
3348: ap_invoice_distributions_all apd,
3349: gms_award_distributions adl
3371:
3372:
3373: -------------------------------------------------------------------------------
3374: -- Procedure marks the current packet's interface data as failed.
3375: -- Updates gms_bc_packets and pa_transaction_interface tables.
3376: -------------------------------------------------------------------------------
3377:
3378: Procedure Mark_Xface_Item_AS_Failed(p_packet_id IN NUMBER,
3379: p_status OUT NOCOPY VARCHAR2) is
3399:
3400: if c1%FOUND then
3401: p_status := 'F';
3402:
3403: update pa_transaction_interface
3404: set transaction_rejection_code = 'GMS_FC_ERROR',
3405: transaction_status_code = 'R'
3406: where txn_interface_id = v_txn_interface_id;
3407: