[Home] [Help]
144: End If;
145:
146: Mark_RCV_PAflag();
147:
148: IF (G_RCV_TRANSACTIONS_MARKED_O > 0) THEN
149:
150: G_err_stage := 'Before calling transfer receipts to pa procedure';
151: If g_body_debug_mode = 'Y' Then
152: write_log(LOG, 'No rcv transctions to be transferred.');
561: G_NUM_AP_SAT_VARIANCE_MARKED_W :=0; /*Bug 13602288: Self Assessed Tax */
562: G_NUM_AP_VARIANCE_PROCESSED :=0;
563:
564: --Added for PO RECEIPT
565: G_RCV_TRANSACTIONS_MARKED_O :=0;
566: G_RCV_TRANSACTIONS_MARKED_J :=0;
567: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
568: G_RCV_TRANSACTIONS_MARKED_G :=0;
569: G_NUM_RCV_TXN_PROCESSED :=0;
562: G_NUM_AP_VARIANCE_PROCESSED :=0;
563:
564: --Added for PO RECEIPT
565: G_RCV_TRANSACTIONS_MARKED_O :=0;
566: G_RCV_TRANSACTIONS_MARKED_J :=0;
567: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
568: G_RCV_TRANSACTIONS_MARKED_G :=0;
569: G_NUM_RCV_TXN_PROCESSED :=0;
570: G_NUM_RCVTAX_PROCESSED :=0;
563:
564: --Added for PO RECEIPT
565: G_RCV_TRANSACTIONS_MARKED_O :=0;
566: G_RCV_TRANSACTIONS_MARKED_J :=0;
567: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
568: G_RCV_TRANSACTIONS_MARKED_G :=0;
569: G_NUM_RCV_TXN_PROCESSED :=0;
570: G_NUM_RCVTAX_PROCESSED :=0;
571:
564: --Added for PO RECEIPT
565: G_RCV_TRANSACTIONS_MARKED_O :=0;
566: G_RCV_TRANSACTIONS_MARKED_J :=0;
567: G_RCV_TRANSACTIONS_MARKED_NULL :=0;
568: G_RCV_TRANSACTIONS_MARKED_G :=0;
569: G_NUM_RCV_TXN_PROCESSED :=0;
570: G_NUM_RCVTAX_PROCESSED :=0;
571:
572: /* restore the old G_err_stack */
5053: tieback_AP_ER(G_TRANSACTION_SOURCE, G_AP_FRT_BATCH_NAME, G_AP_FRT_INTERFACE_ID);
5054:
5055: END IF; /* IF (nvl(v_num_inv_frt_lines_fetched,0) > 0*/
5056:
5057: write_log(LOG,'Calling tieback for locked RCV transactions');
5058: tieback_locked_rcvtxn;
5059:
5060: /*** Commented 3922679 *
5061: G_err_stage := 'Before calling commit';
5428:
5429: G_err_stage := 'Within calling lock_rcv_txn';
5430: write_log(LOG, G_err_stage);
5431:
5432: --pricing changes, updating sub_ledger instead of rcv_transactions
5433: -- Modified this update for bug 6825742
5434:
5435: UPDATE rcv_receiving_sub_ledger rcv_sub
5436: SET rcv_sub.pa_addition_flag = 'L'
5445: /* UPDATE rcv_receiving_sub_ledger rcv_sub
5446: SET rcv_sub.pa_addition_flag = 'L'
5447: WHERE rcv_sub.pa_addition_flag = 'N'
5448: AND EXISTS (SELECT transaction_id
5449: FROM rcv_transactions rcv_txn
5450: WHERE rcv_txn.transaction_id = rcv_sub.rcv_transaction_id
5451: AND rcv_txn.po_distribution_id = p_po_distribution_id ); commented for bug 6825742*/
5452:
5453: l_num_rows := SQL%ROWCOUNT;
5451: AND rcv_txn.po_distribution_id = p_po_distribution_id ); commented for bug 6825742*/
5452:
5453: l_num_rows := SQL%ROWCOUNT;
5454:
5455: write_log(LOG,'number of RCV transactions locked:'||l_num_rows);
5456:
5457: EXCEPTION
5458: WHEN NO_DATA_FOUND THEN
5459: NULL;
5479: WHERE rcv_sub.pa_addition_flag = 'L';
5480:
5481: l_num_rows := SQL%ROWCOUNT;
5482:
5483: write_log(LOG,'number of RCV transactions unlocked:'||l_num_rows);
5484:
5485: EXCEPTION
5486: WHEN NO_DATA_FOUND THEN
5487: NULL;
6890:
6891: BEGIN
6892:
6893: G_err_stack := G_err_stack || '->PAAPIMP_PKG.mark_RCV_PAflag';
6894: write_log(LOG, 'UPDATING RCV_TRANSACTIONS -Marking Process');
6895:
6896: /* mark all the rcv sub ledger records pa_addition_flag to NULL for any rcv transactions
6897: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
6898: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
6892:
6893: G_err_stack := G_err_stack || '->PAAPIMP_PKG.mark_RCV_PAflag';
6894: write_log(LOG, 'UPDATING RCV_TRANSACTIONS -Marking Process');
6895:
6896: /* mark all the rcv sub ledger records pa_addition_flag to NULL for any rcv transactions
6897: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
6898: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
6899:
6900:
6897: that are non-project related or with a non-EXPENSE destination_type_code,which is not a return or is not actual*/
6898: -- This update has been moved to pa_po_integration_utils.update_pa_addition_flag.
6899:
6900:
6901: /* mark rcv sub ledger records pa_addition_flag to O/J for rcv transactions that are
6902: project related and fit the criteria to be pulled into Projects.
6903: Divided into project_ID IS NULL and NOT NULL section for better performance
6904: */
6905:
6919: rcv_sub.program_application_id = G_PROG_APPL_ID,
6920: rcv_sub.program_update_date = SYSDATE
6921: WHERE exists
6922: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
6923: FROM Rcv_Transactions rcv_txn,
6924: PO_Distributions po_dist,
6925: po_lines_all POL
6926: WHERE ((rcv_txn.destination_type_code ='EXPENSE' )
6927: OR (rcv_txn.destination_type_code = 'RECEIVING'
6983: )
6984: AND rcv_sub.pa_addition_flag IN ('N','I')
6985: AND rcv_sub.actual_flag = 'A';
6986:
6987: G_RCV_TRANSACTIONS_MARKED_O := SQL%ROWCOUNT;
6988: write_log(LOG, 'Number of rcvtxn marked to O or J:' || to_char(SQL%ROWCOUNT));
6989:
6990:
6991: ELSIF G_PROJECT_ID IS NULL THEN
7004: rcv_sub.program_application_id = G_PROG_APPL_ID,
7005: rcv_sub.program_update_date = SYSDATE
7006: WHERE exists
7007: (SELECT 1 --rcv_sub1.ROWID --Removed /*+ leading(po_dist) */ for Bug5262594
7008: FROM Rcv_Transactions rcv_txn,
7009: PO_Distributions po_dist ,
7010: po_lines_all POL
7011: WHERE
7012: ((rcv_txn.destination_type_code ='EXPENSE' )
7071: AND rcv_sub.pa_addition_flag IN ('N','I')
7072: AND rcv_sub.actual_flag = 'A';
7073:
7074:
7075: G_RCV_TRANSACTIONS_MARKED_O := SQL%ROWCOUNT;
7076: write_log(LOG, 'Number of rcvtxn marked to O or J:' || to_char(SQL%ROWCOUNT));
7077:
7078: END IF;
7079:
7741: BEGIN
7742:
7743: SELECT primary_quantity, amount
7744: INTO l_primary_quantity, l_amount
7745: FROM rcv_transactions
7746: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
7747:
7748: IF ((( ( l_quantity_tbl(i) <> 0 AND l_primary_quantity = l_quantity_tbl(i)) OR
7749: ( l_amount = l_amount_tbl(i))) AND
7852: BEGIN
7853:
7854: SELECT primary_quantity, amount
7855: INTO l_primary_quantity, l_amount
7856: FROM rcv_transactions
7857: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
7858:
7859: IF ((( ( l_quantity_tbl(i) <> 0 AND l_primary_quantity = l_quantity_tbl(i)) OR
7860: ( l_amount = l_amount_tbl(i))) AND
8124: BEGIN
8125:
8126: SELECT primary_quantity, nvl(amount,0)
8127: INTO l_primary_quantity, l_amount
8128: FROM rcv_transactions
8129: WHERE transaction_id = l_parent_rcv_id_tbl(i) ;
8130:
8131: -- Added a new variable l_po_tax_qty_tbl which will store the original po qty since the l_quantity_tbl
8132: -- variable gets overwritten by the raw cost
8576: v_status VARCHAR2(15);
8577:
8578: BEGIN
8579:
8580: G_err_stage:='Clearing PLSQL tables in RCV transactions tieback';
8581: write_log(LOG, G_err_stage);
8582:
8583: l_sys_ref1_tbl.delete;
8584: l_sys_ref2_tbl.delete;
8600: v_status VARCHAR2(15);
8601:
8602: BEGIN
8603:
8604: G_err_stage:='Within process_tieback of RCV Transactions tieback';
8605: write_log(LOG, G_err_stage);
8606:
8607: FOR i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST LOOP
8608:
8606:
8607: FOR i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST LOOP
8608:
8609: /* If transaction import stamps the record to be 'A' then
8610: update pa_addition_flag of RCV transactions to 'Y'.
8611: If transaction import leaves the record to be 'P' then
8612: update pa_addition_flag of RCV transactions to 'N' or 'I'.
8613: If transaction import stamps the record to be 'R' then
8614: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
8608:
8609: /* If transaction import stamps the record to be 'A' then
8610: update pa_addition_flag of RCV transactions to 'Y'.
8611: If transaction import leaves the record to be 'P' then
8612: update pa_addition_flag of RCV transactions to 'N' or 'I'.
8613: If transaction import stamps the record to be 'R' then
8614: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
8615:
8616: write_log(LOG,'Tying back transaction ID: '||l_sys_ref4_tbl(i)||
8610: update pa_addition_flag of RCV transactions to 'Y'.
8611: If transaction import leaves the record to be 'P' then
8612: update pa_addition_flag of RCV transactions to 'N' or 'I'.
8613: If transaction import stamps the record to be 'R' then
8614: update pa_addition_flag of RCV transactions to 'N' or 'I'.*/
8615:
8616: write_log(LOG,'Tying back transaction ID: '||l_sys_ref4_tbl(i)||
8617: 'trc src: '||l_txn_src_tbl(i));
8618:
8656: v_status VARCHAR2(15);
8657:
8658: BEGIN
8659:
8660: G_err_stage:=('Within bulk update of RCV transactions tieback');
8661: write_log(LOG, G_err_stage);
8662:
8663: FORALL i IN l_sys_ref1_tbl.FIRST..l_sys_ref1_tbl.LAST
8664:
8670: AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_id_tbl(i) OR rcv_sub.accounting_event_id IS NULL)
8671: AND rcv_sub.code_combination_id = l_dr_ccid_tbl(i)
8672: /* Start added for bug#6015451 */
8673: AND EXISTS
8674: (SELECT 1 from rcv_transactions rcv_txn , po_lines_all pol WHERE
8675: rcv_txn.transaction_id = l_sys_ref4_tbl(i)
8676: and rcv_txn.PO_line_ID = POL.po_line_id
8677: AND ( (rcv_txn.destination_type_code = 'EXPENSE' AND /*Starts-code changes for bug:9196652 */
8678: (( rcv_txn.transaction_type = 'DELIVER' AND
8713: /* Ends added for bug#6015451 */
8714:
8715:
8716: l_rows_tiebacked := SQL%ROWCOUNT;
8717: write_log(LOG,'Number of RCV transactions tied back: '||l_rows_tiebacked);
8718:
8719: EXCEPTION
8720: WHEN OTHERS THEN
8721: G_err_stage:= 'Failed during bulk update of RCV transactions tieback';
8717: write_log(LOG,'Number of RCV transactions tied back: '||l_rows_tiebacked);
8718:
8719: EXCEPTION
8720: WHEN OTHERS THEN
8721: G_err_stage:= 'Failed during bulk update of RCV transactions tieback';
8722: write_log(LOG, G_err_stage);
8723: G_err_code := SQLCODE;
8724: raise;
8725:
9013: AND ap1.Invoice_Type_lookup_Code <> 'PREPAYMENT'
9014: AND apdist1.Line_Type_Lookup_Code <> 'PREPAY' /*Changes for bug 7650946 -- End */
9015: )
9016: OR EXISTS ( SELECT rcv_txn.po_distribution_id
9017: FROM rcv_transactions rcv_txn
9018: , rcv_receiving_sub_ledger rcv_sub
9019: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
9020: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
9021: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
9088: AND ap1.Invoice_Type_lookup_Code <> 'PREPAYMENT'
9089: AND apdist1.Line_Type_Lookup_Code <> 'PREPAY' /*Changes for bug 7650946 -- End
9090: )
9091: OR EXISTS ( SELECT rcv_txn.po_distribution_id
9092: FROM rcv_transactions rcv_txn
9093: , rcv_receiving_sub_ledger rcv_sub
9094: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
9095: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
9096: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
9234: AND ap1.Invoice_Type_lookup_Code <> 'PREPAYMENT'
9235: AND apdist1.Line_Type_Lookup_Code <> 'PREPAY' /*Changes for bug 7650946 -- End */
9236: )
9237: OR EXISTS ( SELECT rcv_txn.po_distribution_id
9238: FROM rcv_transactions rcv_txn
9239: , rcv_receiving_sub_ledger rcv_sub
9240: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
9241: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
9242: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
9309: AND ap1.Invoice_Type_lookup_Code <> 'PREPAYMENT'
9310: AND apdist1.Line_Type_Lookup_Code <> 'PREPAY' /*Changes for bug 7650946 -- End
9311: )
9312: OR EXISTS ( SELECT rcv_txn.po_distribution_id
9313: FROM rcv_transactions rcv_txn
9314: , rcv_receiving_sub_ledger rcv_sub
9315: WHERE rcv_txn.po_distribution_id = dist.po_distribution_id
9316: AND rcv_sub.pa_addition_flag || '' IN ('Y','I')
9317: AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
13142: SELECT 'X'
13143: INTO l_Dummy
13144: FROM DUAL
13145: WHERE EXISTS ( SELECT NULL
13146: FROM rcv_transactions rcv_txn2
13147: ,rcv_receiving_sub_ledger rcv_sub2
13148: WHERE rcv_sub2.rcv_transaction_id = rcv_txn2.transaction_id
13149: AND rcv_txn2.po_distribution_id = P_Po_Distribution_Id
13150: AND rcv_sub2.pa_addition_flag = 'G'