VIEW: APPS.PA_INV_BURDEN_DETAILS_LM_V

Source

View Text - Preformatted

SELECT ibdv.project_number ,ibdv.project_name ,ibdv.project_id ,ibdv.draft_invoice_num ,ibdv.ra_invoice_number ,ibdv.bill_through_date ,ibdv.released_date ,ibdv.invoice_date ,ibdv.gl_date ,ibdv.draft_invoice_item_line_num ,ibdv.expenditure_item_id ,ibdv.rdl_line_num ,ibdv.task_number ,ibdv.task_name ,ibdv.task_id ,ibdv.expenditure_organization ,ibdv.expenditure_organization_id ,ibdv.expenditure_type ,ibdv.expenditure_item_date ,ibdv.raw_cost ,ibdv.project_currency_code ,ibdv.bill_amount ,ibdv.burden_cost ,ibdv.cost_base ,ibdv.cb_sort_order ,ibdv.burden_cost_code ,ibdv.bcc_precedence ,ibdv.input_multiplier ,ibdv.compiled_multiplier ,ibdv.burden_schedule ,ibdv.burden_schedule_id ,ibdv.burden_schedule_revision ,ibdv.burden_schedule_revision_id ,ibdv.burden_structure ,ibdv.compiled_set_id FROM pa_inv_burden_details_v ibdv UNION ALL SELECT ibdv.project_number ,ibdv.project_name ,ibdv.project_id ,ibdv.draft_invoice_num ,ibdv.ra_invoice_number ,ibdv.bill_through_date ,ibdv.released_date ,ibdv.invoice_date ,ibdv.gl_date ,ibdv.draft_invoice_item_line_num ,ibdv.expenditure_item_id ,ibdv.rdl_line_num ,ibdv.task_number ,ibdv.task_name ,ibdv.task_id ,ibdv.expenditure_organization ,ibdv.expenditure_organization_id ,ibdv.expenditure_type ,ibdv.expenditure_item_date ,ibdv.raw_cost ,ibdv.project_currency_code ,ibdv.bill_amount ,ibdv.bill_amount - (ibdv.raw_cost * (1+sum(ibdv.compiled_multiplier))) ,ibdv.cost_base ,ibdv.cb_sort_order ,substr(lmlup.meaning,1,30) ,max(ibdv.bcc_precedence) + 1 ,(ibdv.bill_amount - (ibdv.raw_cost * (1+ sum(ibdv.compiled_multiplier)))) / (ibdv.raw_cost * (1+sum(ibdv.compiled_multiplier))) ,decode( (ibdv.bill_amount - (ibdv.raw_cost * (1+ sum(ibdv.compiled_multiplier)))) / (ibdv.raw_cost * (1+sum(ibdv.compiled_multiplier))), 0,0, (ibdv.bill_amount - (ibdv.raw_cost * (1+ sum(ibdv.compiled_multiplier)))) / ibdv.raw_cost) ,ibdv.burden_schedule ,ibdv.burden_schedule_id ,ibdv.burden_schedule_revision ,ibdv.burden_schedule_revision_id ,ibdv.burden_structure ,ibdv.compiled_set_id FROM pa_inv_burden_details_v ibdv, pa_lookups lmlup WHERE lmlup.lookup_type = 'TRANSLATION' AND lmlup.lookup_code = 'LABOR MULTIPLIER BCC' GROUP BY ibdv.project_number ,ibdv.project_name ,ibdv.project_id ,ibdv.draft_invoice_num ,ibdv.ra_invoice_number ,ibdv.bill_through_date ,ibdv.released_date ,ibdv.invoice_date ,ibdv.gl_date ,ibdv.draft_invoice_item_line_num ,ibdv.expenditure_item_id ,ibdv.rdl_line_num ,ibdv.task_number ,ibdv.task_name ,ibdv.task_id ,ibdv.expenditure_organization ,ibdv.expenditure_organization_id ,ibdv.expenditure_type ,ibdv.expenditure_item_date ,ibdv.raw_cost ,ibdv.project_currency_code ,ibdv.bill_amount ,ibdv.cost_base ,ibdv.cb_sort_order ,substr(lmlup.meaning,1,30) ,ibdv.burden_schedule ,ibdv.burden_schedule_id ,ibdv.burden_schedule_revision ,ibdv.burden_schedule_revision_id ,ibdv.burden_structure ,ibdv.compiled_set_id HAVING ibdv.bill_amount != (ibdv.raw_cost * (1+sum(ibdv.compiled_multiplier)))

View Text - HTML Formatted

SELECT IBDV.PROJECT_NUMBER
, IBDV.PROJECT_NAME
, IBDV.PROJECT_ID
, IBDV.DRAFT_INVOICE_NUM
, IBDV.RA_INVOICE_NUMBER
, IBDV.BILL_THROUGH_DATE
, IBDV.RELEASED_DATE
, IBDV.INVOICE_DATE
, IBDV.GL_DATE
, IBDV.DRAFT_INVOICE_ITEM_LINE_NUM
, IBDV.EXPENDITURE_ITEM_ID
, IBDV.RDL_LINE_NUM
, IBDV.TASK_NUMBER
, IBDV.TASK_NAME
, IBDV.TASK_ID
, IBDV.EXPENDITURE_ORGANIZATION
, IBDV.EXPENDITURE_ORGANIZATION_ID
, IBDV.EXPENDITURE_TYPE
, IBDV.EXPENDITURE_ITEM_DATE
, IBDV.RAW_COST
, IBDV.PROJECT_CURRENCY_CODE
, IBDV.BILL_AMOUNT
, IBDV.BURDEN_COST
, IBDV.COST_BASE
, IBDV.CB_SORT_ORDER
, IBDV.BURDEN_COST_CODE
, IBDV.BCC_PRECEDENCE
, IBDV.INPUT_MULTIPLIER
, IBDV.COMPILED_MULTIPLIER
, IBDV.BURDEN_SCHEDULE
, IBDV.BURDEN_SCHEDULE_ID
, IBDV.BURDEN_SCHEDULE_REVISION
, IBDV.BURDEN_SCHEDULE_REVISION_ID
, IBDV.BURDEN_STRUCTURE
, IBDV.COMPILED_SET_ID
FROM PA_INV_BURDEN_DETAILS_V IBDV UNION ALL SELECT IBDV.PROJECT_NUMBER
, IBDV.PROJECT_NAME
, IBDV.PROJECT_ID
, IBDV.DRAFT_INVOICE_NUM
, IBDV.RA_INVOICE_NUMBER
, IBDV.BILL_THROUGH_DATE
, IBDV.RELEASED_DATE
, IBDV.INVOICE_DATE
, IBDV.GL_DATE
, IBDV.DRAFT_INVOICE_ITEM_LINE_NUM
, IBDV.EXPENDITURE_ITEM_ID
, IBDV.RDL_LINE_NUM
, IBDV.TASK_NUMBER
, IBDV.TASK_NAME
, IBDV.TASK_ID
, IBDV.EXPENDITURE_ORGANIZATION
, IBDV.EXPENDITURE_ORGANIZATION_ID
, IBDV.EXPENDITURE_TYPE
, IBDV.EXPENDITURE_ITEM_DATE
, IBDV.RAW_COST
, IBDV.PROJECT_CURRENCY_CODE
, IBDV.BILL_AMOUNT
, IBDV.BILL_AMOUNT - (IBDV.RAW_COST * (1+SUM(IBDV.COMPILED_MULTIPLIER)))
, IBDV.COST_BASE
, IBDV.CB_SORT_ORDER
, SUBSTR(LMLUP.MEANING
, 1
, 30)
, MAX(IBDV.BCC_PRECEDENCE) + 1
, (IBDV.BILL_AMOUNT - (IBDV.RAW_COST * (1+ SUM(IBDV.COMPILED_MULTIPLIER)))) / (IBDV.RAW_COST * (1+SUM(IBDV.COMPILED_MULTIPLIER)))
, DECODE( (IBDV.BILL_AMOUNT - (IBDV.RAW_COST * (1+ SUM(IBDV.COMPILED_MULTIPLIER)))) / (IBDV.RAW_COST * (1+SUM(IBDV.COMPILED_MULTIPLIER)))
, 0
, 0
, (IBDV.BILL_AMOUNT - (IBDV.RAW_COST * (1+ SUM(IBDV.COMPILED_MULTIPLIER)))) / IBDV.RAW_COST)
, IBDV.BURDEN_SCHEDULE
, IBDV.BURDEN_SCHEDULE_ID
, IBDV.BURDEN_SCHEDULE_REVISION
, IBDV.BURDEN_SCHEDULE_REVISION_ID
, IBDV.BURDEN_STRUCTURE
, IBDV.COMPILED_SET_ID
FROM PA_INV_BURDEN_DETAILS_V IBDV
, PA_LOOKUPS LMLUP
WHERE LMLUP.LOOKUP_TYPE = 'TRANSLATION'
AND LMLUP.LOOKUP_CODE = 'LABOR MULTIPLIER BCC' GROUP BY IBDV.PROJECT_NUMBER
, IBDV.PROJECT_NAME
, IBDV.PROJECT_ID
, IBDV.DRAFT_INVOICE_NUM
, IBDV.RA_INVOICE_NUMBER
, IBDV.BILL_THROUGH_DATE
, IBDV.RELEASED_DATE
, IBDV.INVOICE_DATE
, IBDV.GL_DATE
, IBDV.DRAFT_INVOICE_ITEM_LINE_NUM
, IBDV.EXPENDITURE_ITEM_ID
, IBDV.RDL_LINE_NUM
, IBDV.TASK_NUMBER
, IBDV.TASK_NAME
, IBDV.TASK_ID
, IBDV.EXPENDITURE_ORGANIZATION
, IBDV.EXPENDITURE_ORGANIZATION_ID
, IBDV.EXPENDITURE_TYPE
, IBDV.EXPENDITURE_ITEM_DATE
, IBDV.RAW_COST
, IBDV.PROJECT_CURRENCY_CODE
, IBDV.BILL_AMOUNT
, IBDV.COST_BASE
, IBDV.CB_SORT_ORDER
, SUBSTR(LMLUP.MEANING
, 1
, 30)
, IBDV.BURDEN_SCHEDULE
, IBDV.BURDEN_SCHEDULE_ID
, IBDV.BURDEN_SCHEDULE_REVISION
, IBDV.BURDEN_SCHEDULE_REVISION_ID
, IBDV.BURDEN_STRUCTURE
, IBDV.COMPILED_SET_ID HAVING IBDV.BILL_AMOUNT != (IBDV.RAW_COST * (1+SUM(IBDV.COMPILED_MULTIPLIER)))