[Home] [Help]
3166: l_org_id HR_ALL_ORGANIZATION_UNITS.organization_id%type;
3167: l_distribution_id_tbl po_tbl_number;
3168: l_document_id NUMBER;
3169: l_document_id_tbl po_tbl_number;
3170: l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3171: l_base_currency_code GL_CURRENCIES.currency_code%TYPE;
3172: l_doc_currency_code GL_CURRENCIES.currency_code%TYPE;
3173: l_temp_table_row_count NUMBER;
3174:
3318: --Name: populate_temp_table
3319: --Pre-reqs:
3320: -- None.
3321: --Modifies:
3322: -- PO_DOCUMENT_TOTALS_GT
3323: --Locks:
3324: -- None.
3325: --Function:
3326: -- Based on given doc type and doc level, get the list of distribution ids.
3352: -- is assumed
3353: --OUT:
3354: --x_temp_table_key
3355: -- The unique key value that identifies all rows inserted into
3356: -- PO_DOCUMENT_TOTALS_GT for this transaction
3357: --x_count
3358: -- The number of rows inserted into the temp table
3359: --Testing:
3360: --
3375: d_position NUMBER := 0;
3376:
3377: l_distribution_id_tbl PO_TBL_NUMBER;
3378: l_distribution_rev_num_tbl po_tbl_number;
3379: l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3380: l_distribution_type_filter PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
3381: BEGIN
3382:
3383: IF PO_LOG.d_proc THEN
3388:
3389: d_position := 5;
3390:
3391: -- Select the unique key to identify GTT rows for this trxn
3392: SELECT PO_DOCUMENT_TOTALS_GT_S.nextval INTO l_temp_table_key from dual;
3393:
3394: IF p_data_source = g_data_source_TRANSACTION THEN
3395:
3396: d_position := 10;
3413: -- p_doc_level for totals as DISTRIBUTION, then set these line location
3414: -- fields to zero.
3415: -- SQL WHERE: All distributions under the given document level
3416: FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3417: INSERT INTO PO_DOCUMENT_TOTALS_GT
3418: (
3419: key,
3420: line_location_id,
3421: amount_based_flag,
3506: -- p_doc_level for totals as DISTRIBUTION, then set these line location
3507: -- fields to zero.
3508: -- SQL WHERE: All distributions under the given document level
3509: FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3510: INSERT INTO PO_DOCUMENT_TOTALS_GT
3511: (
3512: key,
3513: line_location_id,
3514: amount_based_flag,
3613: -------------------------------------------------------------------------------
3614: --Start of Comments
3615: --Name: prepare_temp_table_data
3616: --Pre-reqs:
3617: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately.
3618: --Modifies:
3619: -- PO_DOCUMENT_TOTALS_GT
3620: --Locks:
3621: -- None.
3615: --Name: prepare_temp_table_data
3616: --Pre-reqs:
3617: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately.
3618: --Modifies:
3619: -- PO_DOCUMENT_TOTALS_GT
3620: --Locks:
3621: -- None.
3622: --Function:
3623: -- Performs intermediate calculations on input data to the GTT.
3625: -- * Performs rounding on the calculated amount_total
3626: --Parameters:
3627: --IN:
3628: --p_temp_table_key
3629: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3630: -- related to this transaction
3631: --p_document_id
3632: -- The po_header_id for POs; the po_release_id for Releases
3633: --Testing:
3674: d_position := 20;
3675:
3676: --SQL What: Calculate the amount columns for quantity based rows
3677: --SQL Where: Quantity-based rows
3678: UPDATE PO_DOCUMENT_TOTALS_GT GTT
3679: SET
3680: GTT.amount_total = (GTT.quantity_total * GTT.price)
3681: , GTT.amount_billed = (GTT.quantity_billed * GTT.price)
3682: , GTT.amount_delivered = (GTT.quantity_delivered * GTT.price)
3690: d_position := 30;
3691:
3692: --SQL What: Round the calculated amounts to correct precision
3693: --SQL Where: Quantity-based rows
3694: UPDATE PO_DOCUMENT_TOTALS_GT GTT
3695: SET
3696: GTT.amount_total = nvl2(l_mau
3697: , round(amount_total/l_mau) * l_mau
3698: , round(amount_total, l_precision))
3737: -------------------------------------------------------------------------------
3738: --Start of Comments
3739: --Name: calculate_totals
3740: --Pre-reqs:
3741: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately
3742: --Modifies:
3743: -- None.
3744: --Locks:
3745: -- None.
3748: -- columns (total order, billed, etc.). Handles normal and Complex Work cases.
3749: --Parameters:
3750: --IN:
3751: --p_temp_table_key
3752: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3753: -- related to this transaction
3754: --p_document_id
3755: -- The po_header_id for POs; the po_release_id for Releases
3756: --p_doc_level
3913: l_amount_financed,
3914: l_amount_recouped,
3915: l_retainage_withheld_amount,
3916: l_retainage_released_amount
3917: FROM PO_DOCUMENT_TOTALS_GT GTT
3918: WHERE key = p_temp_table_key
3919: ;
3920:
3921: d_position := 20;
3948: l_quantity_shipped_actuals,
3949: l_quantity_billed,
3950: l_quantity_financed,
3951: l_quantity_recouped
3952: FROM PO_DOCUMENT_TOTALS_GT GTT
3953: WHERE GTT.key = p_temp_table_key
3954: AND GTT.amount_based_flag = 'N'
3955: AND nvl(GTT.payment_type, 'NULL') <> C_payment_type_RATE
3956: ;
4021: THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_financing,
4022: SUM(GTT.quantity_billed) qty_billed,
4023: SUM(GTT.quantity_financed) qty_financed,
4024: SUM(GTT.quantity_recouped) qty_recouped
4025: FROM PO_DOCUMENT_TOTALS_GT GTT
4026: WHERE GTT.key = p_temp_table_key
4027: AND GTT.amount_based_flag = 'N'
4028: AND nvl(GTT.payment_type, 'NULL') = C_payment_type_MILESTONE
4029: GROUP BY GTT.line_location_id
4114: --Name: clear_temp_table
4115: --Pre-reqs:
4116: -- None.
4117: --Modifies:
4118: -- PO_DOCUMENT_TOTALS_GT
4119: --Locks:
4120: -- None.
4121: --Function:
4122: -- Deletes data from the temp table for this transaction
4122: -- Deletes data from the temp table for this transaction
4123: --Parameters:
4124: --IN:
4125: --p_temp_table_key
4126: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
4127: -- related to this transaction
4128: --Testing:
4129: --
4130: --End of Comments
4146: d_position := 10;
4147:
4148: -- SQL WHAT: Clear the temp table
4149: -- SQL WHERE: All data for this transaction
4150: DELETE FROM PO_DOCUMENT_TOTALS_GT
4151: WHERE key = p_temp_table_key
4152: ;
4153:
4154: d_position := 20;