[Home] [Help]
3285: l_org_id HR_ALL_ORGANIZATION_UNITS.organization_id%type;
3286: l_distribution_id_tbl po_tbl_number;
3287: l_document_id NUMBER;
3288: l_document_id_tbl po_tbl_number;
3289: l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3290: l_base_currency_code GL_CURRENCIES.currency_code%TYPE;
3291: l_doc_currency_code GL_CURRENCIES.currency_code%TYPE;
3292: l_temp_table_row_count NUMBER;
3293:
3437: --Name: populate_temp_table
3438: --Pre-reqs:
3439: -- None.
3440: --Modifies:
3441: -- PO_DOCUMENT_TOTALS_GT
3442: --Locks:
3443: -- None.
3444: --Function:
3445: -- Based on given doc type and doc level, get the list of distribution ids.
3471: -- is assumed
3472: --OUT:
3473: --x_temp_table_key
3474: -- The unique key value that identifies all rows inserted into
3475: -- PO_DOCUMENT_TOTALS_GT for this transaction
3476: --x_count
3477: -- The number of rows inserted into the temp table
3478: --Testing:
3479: --
3494: d_position NUMBER := 0;
3495:
3496: l_distribution_id_tbl PO_TBL_NUMBER;
3497: l_distribution_rev_num_tbl po_tbl_number;
3498: l_temp_table_key PO_DOCUMENT_TOTALS_GT.key%TYPE;
3499: l_distribution_type_filter PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
3500: BEGIN
3501:
3502: IF PO_LOG.d_proc THEN
3507:
3508: d_position := 5;
3509:
3510: -- Select the unique key to identify GTT rows for this trxn
3511: SELECT PO_DOCUMENT_TOTALS_GT_S.nextval INTO l_temp_table_key from dual;
3512:
3513: IF p_data_source = g_data_source_TRANSACTION THEN
3514:
3515: d_position := 10;
3532: -- p_doc_level for totals as DISTRIBUTION, then set these line location
3533: -- fields to zero.
3534: -- SQL WHERE: All distributions under the given document level
3535: FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3536: INSERT INTO PO_DOCUMENT_TOTALS_GT
3537: (
3538: key,
3539: line_location_id,
3540: amount_based_flag,
3625: -- p_doc_level for totals as DISTRIBUTION, then set these line location
3626: -- fields to zero.
3627: -- SQL WHERE: All distributions under the given document level
3628: FORALL i IN 1 .. l_distribution_id_tbl.COUNT
3629: INSERT INTO PO_DOCUMENT_TOTALS_GT
3630: (
3631: key,
3632: line_location_id,
3633: amount_based_flag,
3732: -------------------------------------------------------------------------------
3733: --Start of Comments
3734: --Name: prepare_temp_table_data
3735: --Pre-reqs:
3736: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately.
3737: --Modifies:
3738: -- PO_DOCUMENT_TOTALS_GT
3739: --Locks:
3740: -- None.
3734: --Name: prepare_temp_table_data
3735: --Pre-reqs:
3736: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately.
3737: --Modifies:
3738: -- PO_DOCUMENT_TOTALS_GT
3739: --Locks:
3740: -- None.
3741: --Function:
3742: -- Performs intermediate calculations on input data to the GTT.
3744: -- * Performs rounding on the calculated amount_total
3745: --Parameters:
3746: --IN:
3747: --p_temp_table_key
3748: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3749: -- related to this transaction
3750: --p_document_id
3751: -- The po_header_id for POs; the po_release_id for Releases
3752: --Testing:
3793: d_position := 20;
3794:
3795: --SQL What: Calculate the amount columns for quantity based rows
3796: --SQL Where: Quantity-based rows
3797: UPDATE PO_DOCUMENT_TOTALS_GT GTT
3798: SET
3799: GTT.amount_total = (GTT.quantity_total * GTT.price)
3800: , GTT.amount_billed = (GTT.quantity_billed * GTT.price)
3801: , GTT.amount_delivered = (GTT.quantity_delivered * GTT.price)
3809: d_position := 30;
3810:
3811: --SQL What: Round the calculated amounts to correct precision
3812: --SQL Where: Quantity-based rows
3813: UPDATE PO_DOCUMENT_TOTALS_GT GTT
3814: SET
3815: GTT.amount_total = nvl2(l_mau
3816: , round(amount_total/l_mau) * l_mau
3817: , round(amount_total, l_precision))
3856: -------------------------------------------------------------------------------
3857: --Start of Comments
3858: --Name: calculate_totals
3859: --Pre-reqs:
3860: -- PO_DOCUMENT_TOTALS_GT must be populated appropriately
3861: --Modifies:
3862: -- None.
3863: --Locks:
3864: -- None.
3867: -- columns (total order, billed, etc.). Handles normal and Complex Work cases.
3868: --Parameters:
3869: --IN:
3870: --p_temp_table_key
3871: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
3872: -- related to this transaction
3873: --p_document_id
3874: -- The po_header_id for POs; the po_release_id for Releases
3875: --p_doc_level
4032: l_amount_financed,
4033: l_amount_recouped,
4034: l_retainage_withheld_amount,
4035: l_retainage_released_amount
4036: FROM PO_DOCUMENT_TOTALS_GT GTT
4037: WHERE key = p_temp_table_key
4038: ;
4039:
4040: d_position := 20;
4067: l_quantity_shipped_actuals,
4068: l_quantity_billed,
4069: l_quantity_financed,
4070: l_quantity_recouped
4071: FROM PO_DOCUMENT_TOTALS_GT GTT
4072: WHERE GTT.key = p_temp_table_key
4073: AND GTT.amount_based_flag = 'N'
4074: AND nvl(GTT.payment_type, 'NULL') <> C_payment_type_RATE
4075: ;
4140: THEN GTT.quantity_shipped ELSE 0 END) qty_shipped_financing,
4141: SUM(GTT.quantity_billed) qty_billed,
4142: SUM(GTT.quantity_financed) qty_financed,
4143: SUM(GTT.quantity_recouped) qty_recouped
4144: FROM PO_DOCUMENT_TOTALS_GT GTT
4145: WHERE GTT.key = p_temp_table_key
4146: AND GTT.amount_based_flag = 'N'
4147: AND nvl(GTT.payment_type, 'NULL') = C_payment_type_MILESTONE
4148: GROUP BY GTT.line_location_id
4233: --Name: clear_temp_table
4234: --Pre-reqs:
4235: -- None.
4236: --Modifies:
4237: -- PO_DOCUMENT_TOTALS_GT
4238: --Locks:
4239: -- None.
4240: --Function:
4241: -- Deletes data from the temp table for this transaction
4241: -- Deletes data from the temp table for this transaction
4242: --Parameters:
4243: --IN:
4244: --p_temp_table_key
4245: -- The unique key value that identifies all rows in PO_DOCUMENT_TOTALS_GT
4246: -- related to this transaction
4247: --Testing:
4248: --
4249: --End of Comments
4265: d_position := 10;
4266:
4267: -- SQL WHAT: Clear the temp table
4268: -- SQL WHERE: All data for this transaction
4269: DELETE FROM PO_DOCUMENT_TOTALS_GT
4270: WHERE key = p_temp_table_key
4271: ;
4272:
4273: d_position := 20;