VIEW: APPS.ITG_SP_PO_LINE_DIST_V

Source

View Text - Preformatted

SELECT pda.po_distribution_id, pda.po_header_id, pda.po_line_id, pda.line_location_id, plla.price_override * pda.quantity_ordered operamt_extended, pha.currency_code operamt_currency, pda.quantity_ordered operamt_UOMvalue, uom.uom_code operamt_uom, DECODE(itg_x_utils.getFlexQualifierSegment( gcc.chart_of_accounts_id, 'FA_COST_CTR' ), 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30) costcenter, gcc.segment1 element1, gcc.segment2 element2, gcc.segment3 element3, gcc.segment4 element4, gcc.segment5 element5, gcc.segment6 element6, gcc.segment7 element7, gcc.segment8 element8, gcc.segment9 element9, gcc.segment10 element10, gcc.segment11 element11, gcc.segment12 element12, gcc.segment13 element13, gcc.segment14 element14, gcc.segment15 element15, gcc.segment16 element16, gcc.segment17 element17, gcc.segment18 element18, gcc.segment19 element19, gcc.segment20 element20, gcc.segment21 element21, gcc.segment22 element22, gcc.segment23 element23, gcc.segment24 element24, gcc.segment25 element25, gcc.segment26 element26, gcc.segment27 element27, gcc.segment28 element28, gcc.segment29 element29, gcc.segment30 element30, TO_CHAR(OrgDef.set_of_books_id) glentitys, DECODE(itg_x_utils.getFlexQualifierSegment( gcc.chart_of_accounts_id, 'GL_ACCOUNT' ), 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3, 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6, 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9, 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12, 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15, 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18, 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21, 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24, 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27, 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30) glnomacct, TO_CHAR(pda.project_id) project, OrgDef.organization_name unit, TO_CHAR(pda.po_distribution_id) UA_distrid, TO_CHAR(pda.line_location_id) UA_schedlid, TO_CHAR(pda.distribution_num) UA_linenum, per.employee_number UA_deliverto, pda.attribute1 UA_dist_attr1, pda.attribute2 UA_dist_attr2, pda.attribute3 UA_dist_attr3, pda.attribute4 UA_dist_attr4, pda.attribute5 UA_dist_attr5, pda.attribute6 UA_dist_attr6, pda.attribute7 UA_dist_attr7, pda.attribute8 UA_dist_attr8, pda.attribute9 UA_dist_attr9, pda.attribute10 UA_dist_attr10, pda.attribute11 UA_dist_attr11, pda.attribute12 UA_dist_attr12, pda.attribute13 UA_dist_attr13, pda.attribute14 UA_dist_attr14, pda.attribute15 UA_dist_attr15, itg_x_utils.getRequistnID(pda.req_distribution_id) UA_requisitnid, itg_x_utils.getReqLineNum(pda.req_distribution_id) UA_reqlinenum FROM gl_code_combinations gcc, mtl_units_of_measure uom, org_organization_definitions OrgDef, per_people_f per, po_line_locations_all plla, po_distributions_all pda, po_headers_all pha WHERE gcc.code_combination_id = pda.code_combination_id AND OrgDef.organization_id (+) = pda.destination_organization_id AND plla.line_location_id (+) = pda.line_location_id AND pha.po_header_id = pda.po_header_id AND per.person_id (+) = pda.deliver_to_person_id AND uom.unit_of_measure (+) = plla.unit_meas_lookup_code AND pda.line_location_id IS NOT NULL AND nvl(per.effective_end_date, sysdate+1) > sysdate AND NVL(pda.distribution_type,'xx') <> 'PREPAYMENT' and NVL(plla.shipment_type,'xx') <> 'PREPAYMENT'

View Text - HTML Formatted

SELECT PDA.PO_DISTRIBUTION_ID
, PDA.PO_HEADER_ID
, PDA.PO_LINE_ID
, PDA.LINE_LOCATION_ID
, PLLA.PRICE_OVERRIDE * PDA.QUANTITY_ORDERED OPERAMT_EXTENDED
, PHA.CURRENCY_CODE OPERAMT_CURRENCY
, PDA.QUANTITY_ORDERED OPERAMT_UOMVALUE
, UOM.UOM_CODE OPERAMT_UOM
, DECODE(ITG_X_UTILS.GETFLEXQUALIFIERSEGMENT( GCC.CHART_OF_ACCOUNTS_ID
, 'FA_COST_CTR' )
, 'SEGMENT1'
, GCC.SEGMENT1
, 'SEGMENT2'
, GCC.SEGMENT2
, 'SEGMENT3'
, GCC.SEGMENT3
, 'SEGMENT4'
, GCC.SEGMENT4
, 'SEGMENT5'
, GCC.SEGMENT5
, 'SEGMENT6'
, GCC.SEGMENT6
, 'SEGMENT7'
, GCC.SEGMENT7
, 'SEGMENT8'
, GCC.SEGMENT8
, 'SEGMENT9'
, GCC.SEGMENT9
, 'SEGMENT10'
, GCC.SEGMENT10
, 'SEGMENT11'
, GCC.SEGMENT11
, 'SEGMENT12'
, GCC.SEGMENT12
, 'SEGMENT13'
, GCC.SEGMENT13
, 'SEGMENT14'
, GCC.SEGMENT14
, 'SEGMENT15'
, GCC.SEGMENT15
, 'SEGMENT16'
, GCC.SEGMENT16
, 'SEGMENT17'
, GCC.SEGMENT17
, 'SEGMENT18'
, GCC.SEGMENT18
, 'SEGMENT19'
, GCC.SEGMENT19
, 'SEGMENT20'
, GCC.SEGMENT20
, 'SEGMENT21'
, GCC.SEGMENT21
, 'SEGMENT22'
, GCC.SEGMENT22
, 'SEGMENT23'
, GCC.SEGMENT23
, 'SEGMENT24'
, GCC.SEGMENT24
, 'SEGMENT25'
, GCC.SEGMENT25
, 'SEGMENT26'
, GCC.SEGMENT26
, 'SEGMENT27'
, GCC.SEGMENT27
, 'SEGMENT28'
, GCC.SEGMENT28
, 'SEGMENT29'
, GCC.SEGMENT29
, 'SEGMENT30'
, GCC.SEGMENT30) COSTCENTER
, GCC.SEGMENT1 ELEMENT1
, GCC.SEGMENT2 ELEMENT2
, GCC.SEGMENT3 ELEMENT3
, GCC.SEGMENT4 ELEMENT4
, GCC.SEGMENT5 ELEMENT5
, GCC.SEGMENT6 ELEMENT6
, GCC.SEGMENT7 ELEMENT7
, GCC.SEGMENT8 ELEMENT8
, GCC.SEGMENT9 ELEMENT9
, GCC.SEGMENT10 ELEMENT10
, GCC.SEGMENT11 ELEMENT11
, GCC.SEGMENT12 ELEMENT12
, GCC.SEGMENT13 ELEMENT13
, GCC.SEGMENT14 ELEMENT14
, GCC.SEGMENT15 ELEMENT15
, GCC.SEGMENT16 ELEMENT16
, GCC.SEGMENT17 ELEMENT17
, GCC.SEGMENT18 ELEMENT18
, GCC.SEGMENT19 ELEMENT19
, GCC.SEGMENT20 ELEMENT20
, GCC.SEGMENT21 ELEMENT21
, GCC.SEGMENT22 ELEMENT22
, GCC.SEGMENT23 ELEMENT23
, GCC.SEGMENT24 ELEMENT24
, GCC.SEGMENT25 ELEMENT25
, GCC.SEGMENT26 ELEMENT26
, GCC.SEGMENT27 ELEMENT27
, GCC.SEGMENT28 ELEMENT28
, GCC.SEGMENT29 ELEMENT29
, GCC.SEGMENT30 ELEMENT30
, TO_CHAR(ORGDEF.SET_OF_BOOKS_ID) GLENTITYS
, DECODE(ITG_X_UTILS.GETFLEXQUALIFIERSEGMENT( GCC.CHART_OF_ACCOUNTS_ID
, 'GL_ACCOUNT' )
, 'SEGMENT1'
, GCC.SEGMENT1
, 'SEGMENT2'
, GCC.SEGMENT2
, 'SEGMENT3'
, GCC.SEGMENT3
, 'SEGMENT4'
, GCC.SEGMENT4
, 'SEGMENT5'
, GCC.SEGMENT5
, 'SEGMENT6'
, GCC.SEGMENT6
, 'SEGMENT7'
, GCC.SEGMENT7
, 'SEGMENT8'
, GCC.SEGMENT8
, 'SEGMENT9'
, GCC.SEGMENT9
, 'SEGMENT10'
, GCC.SEGMENT10
, 'SEGMENT11'
, GCC.SEGMENT11
, 'SEGMENT12'
, GCC.SEGMENT12
, 'SEGMENT13'
, GCC.SEGMENT13
, 'SEGMENT14'
, GCC.SEGMENT14
, 'SEGMENT15'
, GCC.SEGMENT15
, 'SEGMENT16'
, GCC.SEGMENT16
, 'SEGMENT17'
, GCC.SEGMENT17
, 'SEGMENT18'
, GCC.SEGMENT18
, 'SEGMENT19'
, GCC.SEGMENT19
, 'SEGMENT20'
, GCC.SEGMENT20
, 'SEGMENT21'
, GCC.SEGMENT21
, 'SEGMENT22'
, GCC.SEGMENT22
, 'SEGMENT23'
, GCC.SEGMENT23
, 'SEGMENT24'
, GCC.SEGMENT24
, 'SEGMENT25'
, GCC.SEGMENT25
, 'SEGMENT26'
, GCC.SEGMENT26
, 'SEGMENT27'
, GCC.SEGMENT27
, 'SEGMENT28'
, GCC.SEGMENT28
, 'SEGMENT29'
, GCC.SEGMENT29
, 'SEGMENT30'
, GCC.SEGMENT30) GLNOMACCT
, TO_CHAR(PDA.PROJECT_ID) PROJECT
, ORGDEF.ORGANIZATION_NAME UNIT
, TO_CHAR(PDA.PO_DISTRIBUTION_ID) UA_DISTRID
, TO_CHAR(PDA.LINE_LOCATION_ID) UA_SCHEDLID
, TO_CHAR(PDA.DISTRIBUTION_NUM) UA_LINENUM
, PER.EMPLOYEE_NUMBER UA_DELIVERTO
, PDA.ATTRIBUTE1 UA_DIST_ATTR1
, PDA.ATTRIBUTE2 UA_DIST_ATTR2
, PDA.ATTRIBUTE3 UA_DIST_ATTR3
, PDA.ATTRIBUTE4 UA_DIST_ATTR4
, PDA.ATTRIBUTE5 UA_DIST_ATTR5
, PDA.ATTRIBUTE6 UA_DIST_ATTR6
, PDA.ATTRIBUTE7 UA_DIST_ATTR7
, PDA.ATTRIBUTE8 UA_DIST_ATTR8
, PDA.ATTRIBUTE9 UA_DIST_ATTR9
, PDA.ATTRIBUTE10 UA_DIST_ATTR10
, PDA.ATTRIBUTE11 UA_DIST_ATTR11
, PDA.ATTRIBUTE12 UA_DIST_ATTR12
, PDA.ATTRIBUTE13 UA_DIST_ATTR13
, PDA.ATTRIBUTE14 UA_DIST_ATTR14
, PDA.ATTRIBUTE15 UA_DIST_ATTR15
, ITG_X_UTILS.GETREQUISTNID(PDA.REQ_DISTRIBUTION_ID) UA_REQUISITNID
, ITG_X_UTILS.GETREQLINENUM(PDA.REQ_DISTRIBUTION_ID) UA_REQLINENUM
FROM GL_CODE_COMBINATIONS GCC
, MTL_UNITS_OF_MEASURE UOM
, ORG_ORGANIZATION_DEFINITIONS ORGDEF
, PER_PEOPLE_F PER
, PO_LINE_LOCATIONS_ALL PLLA
, PO_DISTRIBUTIONS_ALL PDA
, PO_HEADERS_ALL PHA
WHERE GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID
AND ORGDEF.ORGANIZATION_ID (+) = PDA.DESTINATION_ORGANIZATION_ID
AND PLLA.LINE_LOCATION_ID (+) = PDA.LINE_LOCATION_ID
AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
AND PER.PERSON_ID (+) = PDA.DELIVER_TO_PERSON_ID
AND UOM.UNIT_OF_MEASURE (+) = PLLA.UNIT_MEAS_LOOKUP_CODE
AND PDA.LINE_LOCATION_ID IS NOT NULL
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1) > SYSDATE
AND NVL(PDA.DISTRIBUTION_TYPE
, 'XX') <> 'PREPAYMENT'
AND NVL(PLLA.SHIPMENT_TYPE
, 'XX') <> 'PREPAYMENT'