Query (Order Management and Inventory)
1. Query Modifier =
SELECT a.list_header_id, a.NAME, a.version_no, b.start_date_active START_HEAD,
b.end_date_active END_HEAD, b.active_flag,
c.modifier_level, c.list_line_type, c.price_break_type,
c.charge_name, c.formula, c.arithmetic_operator_type, c.operand,
c.arithmetic_operator, c.start_date_active START_LINE, c.end_date_active END_LINE,
c.incompatibility_grp_code, c.list_line_no, c.product_precedence,
c.pricing_attribute_id, c.product_attribute_context, c.product_attr,
c.product_attr_val, c.product_uom_code, c.comparison_operator_code,
c.pricing_attribute_context, c.pricing_attr, c.pricing_attr_value_to,
c.pricing_attr_seg_name, c.prod_attr_segment_name,
c.product_attribute_type, c.product_attr_value, c.pricing_attribute,
d.qualifier_id, d.excluder_flag, d.comparision_operator_code,
d.qualifier_context, d.qualifier_attribute, d.qualifier_grouping_no,
d.qualifier_attr_value, d.start_date_active START_QUALIFIER, d.end_date_active END_QUALIFIER,
d.qualifier_datatype, d.qualifier_precedence, e.customer_name
FROM qp_list_headers_tl a,
qp_list_headers_b b,
qp_modifier_summary_v c,
qp_qualifiers_v d,
ar_customers_v e
WHERE a.list_header_id = b.list_header_id
AND a.list_header_id = c.list_header_id
AND b.list_header_id = c.list_header_id
AND c.list_line_id = d.list_line_id(+)
AND e.customer_id(+) = d.qualifier_attr_value
2. Query MAster Item (Inventory) =
SELECT a.segment1 no_dok,
DECODE (a.status_lookup_code,
‘I’, ‘Incomplete’,
‘A’, ‘Active’,
‘Closed’
) status_quo,
b.vendor_name, c.vendor_site_code, f.INVENTORY_ITEM_ID, f.segment1 ko_bar, f.attribute2 kode_foxpro,
d.item_description, f.PRIMARY_UOM_CODE, d.unit_price hna, e.quantity, e.price_override hjp,
e.price_discount marjin, g.category_concat_segs kls_prod,
h.ATTRIBUTE4 PHB, f.HAZARD_CLASS_ID OKT, i.LOCATION_CODE
FROM po_headers_all a,
po_vendors b,
po_vendor_sites_all c,
po_lines_all d,
po_line_locations_all e,
mtl_system_items_b f,
mtl_item_categories_v g,
mtl_system_items_b h,
hr_locations_all i
WHERE a.type_lookup_code = ‘QUOTATION’
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND a.status_lookup_code ‘C’
AND a.closed_date IS NULL
AND a.po_header_id = d.po_header_id
AND a.po_header_id = e.po_header_id
AND d.po_line_id = e.po_line_id
AND d.item_id = f.inventory_item_id
AND d.item_id = g.inventory_item_id
AND f.inventory_item_id = g.inventory_item_id
AND f.organization_id = ‘105′
and h.INVENTORY_ITEM_ID = f.INVENTORY_ITEM_ID
and h.ORGANIZATION_ID = ‘104′
AND f.organization_id = g.organization_id
and i.LOCATION_ID = e.SHIP_TO_LOCATION_ID
AND g.category_set_id = 1
AND d.po_line_id IN (
SELECT z.po_line_id
FROM (SELECT b.vendor_name, c.vendor_site_code,
MAX (d.po_line_id) po_line_id, f.segment1,
d.item_description
FROM po_headers_all a,
po_vendors b,
po_vendor_sites_all c,
po_lines_all d,
po_line_locations_all e,
mtl_system_items_b f
WHERE a.type_lookup_code = ‘QUOTATION’
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND a.status_lookup_code ‘C’
AND a.closed_date IS NULL
AND a.po_header_id = d.po_header_id
AND a.po_header_id = e.po_header_id
AND d.po_line_id = e.po_line_id
AND d.item_id = f.inventory_item_id
AND f.organization_id = ‘105′
GROUP BY b.vendor_name,
c.vendor_site_code,
f.segment1,
d.item_description) z)
3. Query Locator =
SELECT d.segment1, b.organization_name, a.transaction_quantity,
a.subinventory_code,
c.segment1
|| ‘.’
|| c.segment2
|| ‘.’
|| c.segment3
|| ‘.’
|| c.segment4 LOCATOR,
c.description, a.lot_number
FROM mtl_onhand_quantities a,
org_organization_definitions b,
mtl_item_locations c,
mtl_system_items_b d
WHERE a.organization_id = b.organization_id
AND a.locator_id = c.inventory_location_id(+)
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
ORDER BY d.segment1
4. Query Status =
select
c.NAME,
a.HEADER_ID,
a.ORDER_NUMBER,
d.NAME,
e.name,
a.ORDERED_DATE,
b.CUSTOMER_NAME,
a.FLOW_STATUS_CODE
from OE_ORDER_HEADERS_all a,
ar_customers_v b,
hr_organization_units c,
oe_transaction_types_tl d,
qp_list_headers_tl e,
where a.SOLD_TO_ORG_ID = b.CUSTOMER_ID
and a.ORG_ID = c.ORGANIZATION_ID
and a.ORDER_TYPE_ID = d.TRANSACTION_TYPE_ID
and e.LIST_HEADER_ID = a.PRICE_LIST_ID
and a.ORDER_NUMBER = ‘130109000352′
5. Query SO =
SELECT DISTINCT a.ct_reference, a.trx_number, a.trx_date,
a.interface_header_context sumber, b.customer_name,
c.NAME tipe_transaksi, d.NAME organisasi,
e.customer_trx_line_id, e.line_number, e.quantity_invoiced,
e.unit_selling_price, e.extended_amount, e.revenue_amount,
e.uom_code, e.line_type, e.description,
e.link_to_cust_trx_line_id
FROM ra_customer_trx_all a,
ar_customers_v b,
ra_cust_trx_types_all c,
hr_organization_units d,
ra_customer_trx_lines_all e,
mtl_system_items_b f
WHERE a.sold_to_customer_id = b.customer_id
AND a.cust_trx_type_id = c.cust_trx_type_id
AND a.org_id = d.organization_id
AND a.customer_trx_id = e.customer_trx_id
AND e.inventory_item_id = f.inventory_item_id(+)
6. Query Price list OM =
select qlht.DESCRIPTION, msi.ATTRIBUTE2, msi.DESCRIPTION , qppr.PRODUCT_UOM_CODE, qpll.OPERAND, qpll.START_DATE_ACTIVE, qpll.END_DATE_ACTIVE
from qp_list_headers_b qlhb,qp_list_headers_tl qlht,qp_list_lines qpll, qp_pricing_attributes qppr, MTL_SYSTEM_ITEMS msi
where qlhb.LIST_HEADER_ID = qlht.LIST_HEADER_ID
and qlhb.ACTIVE_FLAG = ‘Y’
and qlhb.END_DATE_ACTIVE is null
and qlhb.LIST_TYPE_CODE = ‘PRL’
and qpll.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and qppr.LIST_LINE_ID = qpll.LIST_LINE_ID
and qppr.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qppr.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘104′
7. Query Customer =
SELECT hps.PARTY_SITE_NUMBER CUSTOMER_NUMBER, hcasa.CUST_ACCOUNT_ID SOLD_TO_ORG_ID, hl.ADDRESS1 NAME
FROM hz_party_sites hps, hz_locations hl, hz_cust_acct_sites_all hcasa
WHERE hl.location_id = hps.location_id and hcasa.party_site_id = hps.party_site_id
ORDER BY hps.party_site_number
8. Query GL =
SELECT (CASE WHEN ABSA.product_precedence IN (210, 230, 220) THEN
(CASE
WHEN otta.order_category_code = ‘RETURN’
THEN (opa.adjusted_amount_per_pqty)
* oola.ordered_quantity
ELSE ABS (opa.adjusted_amount_per_pqty)
* oola.ordered_quantity
END
)
ELSE
(CASE
WHEN otta.order_category_code = ‘RETURN’
THEN (((absa.ATTRIBUTE1*OOLA.UNIT_LIST_PRICE)/100)*OOLA.ORDERED_QUANTITY)
ELSE ABS ((((absa.ATTRIBUTE1*OOLA.UNIT_LIST_PRICE)/100)*OOLA.ORDERED_QUANTITY))
END
)
END) discount_amount,
msi.sales_account ccid, rcta.trx_number, rcta.trx_date,
rcta.invoice_currency_code curr, ooha.order_number,
absa.product_precedence, oola.line_id, oola.ROWID rid
–, gcc.segment1, gcc.segment2,
–gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6
FROM oe_order_lines_all oola,
ra_customer_trx_all rctad,
oe_order_headers_all ooha,
mtl_system_items msi,
oe_price_adjustments opa,
qp_list_headers_tl qlht,
ra_customer_trx_all rcta,
hr_organization_units hou,
so_order_types_all sota,
oe_transaction_types_all otta,
–ra_customer_trx_lines_all rctla,
–gl_code_combinations gcc,
(SELECT /*+ USE_HASH(QLL,QLTB) */
DISTINCT qlt.list_header_id, qll.product_precedence, qll.ATTRIBUTE1
FROM qp_list_headers_tl qlt,
qp_list_headers_b qltb,
qp_list_lines qll
WHERE qll.list_header_id = qlt.list_header_id
AND qll.product_precedence IS NOT NULL
AND qll.product_precedence IN (210, 230, 220, 260,250,290)
AND qltb.list_header_id = qlt.list_header_id
AND qltb.active_flag ‘N’) absa
WHERE msi.organization_id = hou.organization_id
–AND rctla.interface_line_attribute6 = oola.line_id
–AND rctla.customer_trx_id = rcta.customer_trx_id
–AND rctla.inventory_item_id = msi.inventory_item_id
AND rcta.previous_customer_trx_id = rctad.customer_trx_id(+)
–MASTER_ITEM
AND oola.inventory_item_id = msi.inventory_item_id
AND opa.list_header_id = qlht.list_header_id
AND opa.line_id = oola.line_id
AND UPPER (qlht.list_header_id) = absa.list_header_id
–DISCOUNT
AND ooha.header_id = oola.header_id
AND rcta.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND oola.flow_status_code = ‘CLOSED’
AND hou.organization_id = ooha.ship_from_org_id
AND sota.NAME = rcta.interface_header_attribute2
AND sota.order_type_id = ooha.order_type_id
/*AND TO_DATE (rcta.trx_date) BETWEEN TO_DATE
(:p_from_date,
‘YYYY/MM/DD HH24:MI:SS’
)
AND TO_DATE
(:p_to_date,
‘YYYY/MM/DD HH24:MI:SS’
)*/
–PARAMETER DATE
–AND ooha.org_id = :p_org_id
AND otta.transaction_type_id = ooha.order_type_id
AND oola.attribute15 IS NULL;
9. Sales Teritory =
select * from JTF_RS_DEFRESOURCES_VL
RESOURCE_NUMBER
select a.SALESREP_TERRITORY_ID,a.SALESREP_ID,a.TERRITORY_ID ,b.SALESREP_NUMBER
from ra_salesrep_territories a, jtf_rs_salesreps b ,
where a.SALESREP_ID = b.SALESREP_ID;
select SALESREP_ID,NAME,SALESREP_NUMBER from jtf_rs_salesreps
select TERRITORY_ID,name,DESCRIPTION from RA_TERRITORIES
select a.SALESREP_TERRITORY_ID,a.SALESREP_ID,a.TERRITORY_ID ,b.SALESREP_NUMBER
from ra_salesrep_territories a, jtf_rs_salesreps b ,
where a.SALESREP_ID = b.SALESREP_ID;
10.Harga & Customer =
BEGIN
dbms_application_info.set_client_info(103);
end;
select acv.CUSTOMER_NAME, acv.CUSTOMER_NAME_PHONETIC, acv.CUSTOMER_NUMBER,aav.ADDRESS_ID,
aav.PARTY_ID, acv.CUSTOMER_ID,
aav.PARTY_SITE_ID,aav.ADDRESS_LINES_PHONETIC
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
select qlht.DESCRIPTION, msi.ATTRIBUTE2, msi.DESCRIPTION , qppr.PRODUCT_UOM_CODE, qpll.OPERAND, qpll.START_DATE_ACTIVE, qpll.END_DATE_ACTIVE
from qp_list_headers_b qlhb,qp_list_headers_tl qlht,qp_list_lines qpll, qp_pricing_attributes qppr, MTL_SYSTEM_ITEMS msi
where qlhb.LIST_HEADER_ID = qlht.LIST_HEADER_ID
and qlhb.ACTIVE_FLAG = ‘Y’
and qlhb.END_DATE_ACTIVE is null
and qlhb.LIST_TYPE_CODE = ‘PRL’
and qpll.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and qppr.LIST_LINE_ID = qpll.LIST_LINE_ID
and qppr.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qppr.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘104′
11. Query OCHIE =
select otl.NAME, otl.DESCRIPTION
from OE_TRANSACTION_TYPES_ALL ott, OE_TRANSACTION_TYPES_TL otl
where ott.ORG_ID = ‘204′
and ott.TRANSACTION_TYPE_ID = otl.TRANSACTION_TYPE_ID
order by otl.NAME
select ooh.ORDER_NUMBER
from OE_TRANSACTION_TYPES_ALL ott, OE_ORDER_HEADERS_ALL ooh, OE_TRANSACTION_TYPES_TL otl
where ott.ORG_ID = ‘204′
and ott.TRANSACTION_TYPE_ID = ooh.ORDER_TYPE_ID
and otl.TRANSACTION_TYPE_ID = ooh.ORDER_TYPE_ID
and ott.TRANSACTION_TYPE_ID = otl.TRANSACTION_TYPE_ID
order by ooh.ORDER_NUMBER
- aDDitiOnaL SCRipt -
ott.ORG_ID = :$PROFILES$.ORG_ID
:$FLEX$.nama value set
- QueRy PRiCe LiSt -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_secu_list_headers_v qsl, qp_list_lines_v qll
where qsl.ORIG_ORG_ID = ‘107′
and qsl.LIST_HEADER_ID = ‘7064′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
order by qll.PRODUCT_ATTR_VAL_DISP
- UpDatE QueRy PRiCe LiSt -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_list_headers_v qsl, qp_list_lines_v qll
where qsl.ORIG_ORG_ID = ‘107′
and qsl.LIST_HEADER_ID = ‘7064′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
order by qll.PRODUCT_ATTR_VAL_DISP
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, msi.DESCRIPTION,
qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_list_headers_v qsl, qp_list_lines_v qll, MTL_SYSTEM_ITEMS_B msi
where qsl.ORIG_ORG_ID = ‘103′
and qsl.LIST_HEADER_ID = ‘6041′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qll.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘106′
and substr(PRODUCT_ATTR_VAL_DISP,2,1) = ‘R’
order by qll.PRODUCT_ATTR_VAL_DISP
- QueRy ORIG_SYSTEM_REFERENCE -
BEGIN
dbms_application_info.set_client_info(107);
end;
select acv.CUSTOMER_NUMBER, acv.CUSTOMER_NAME, acv.ORIG_SYSTEM_REFERENCE,
aav.CONCATENATED_ADDRESS, aav.ORIG_SYSTEM_REFERENCE
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
- QueRy teRRitORieS -
BEGIN
dbms_application_info.set_client_info(107);
end;
select acv.CUSTOMER_NUMBER, rta.NAME, rta.SEGMENT1, rta.SEGMENT2
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav, HZ_SITE_USES_V hsv, HZ_CUST_SITE_USES_ALL hcs,
RA_TERRITORIES rta
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
and aav.ADDRESS_ID = hsv.ADDRESS_ID
and hsv.SITE_USE_ID = hcs.SITE_USE_ID
and hcs.TERRITORY_ID = rta.TERRITORY_ID
order by acv.CUSTOMER_NUMBER
- QueRy mODiFieR nUmbeR -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qms.LIST_LINE_ID, qms.OPERAND, qms.LIST_LINE_NO, qms.PRODUCT_ATTR_VALUE
from qp_modifier_summary_v qms
where qms.LIST_HEADER_ID = ‘7133′
_aDDitiOnaL SCRipt_
SELECT ffvt.description
INTO v_desc
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv, fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_name = ‘MPI_Assisten Apoteker’
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvt.flex_value_id = ffv.flex_value_id
AND ffv.enabled_flag = ‘Y’
AND ffvt.flex_value_meaning = :attribute3;
- QueRy ItEmS -
select msi.SEGMENT1, msi.DESCRIPTION, msi.WEIGHT_UOM_CODE, msi.PRIMARY_UNIT_OF_MEASURE,
msi.PRIMARY_UOM_CODE
from MTL_SYSTEM_ITEMS_B msi
where msi.ORGANIZATION_ID = ‘108′
and msi.INVENTORY_ITEM_STATUS_CODE = ‘Active’
and substr(SEGMENT1,2,1) = ‘B’
order by msi.SEGMENT1
- QueRy CuStOmeR -
select acv.CUSTOMER_NAME, acv.CUSTOMER_NAME_PHONETIC, acv.CUSTOMER_NUMBER,aav.ADDRESS_ID,
aav.PARTY_ID,
aav.PARTY_SITE_ID,aav.ADDRESS_LINES_PHONETIC
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
- QueRy SaLeS PeRSon -
BEGIN
dbms_application_info.set_client_info(103);
end;
select acv.CUSTOMER_NUMBER, rta.NAME, rta.SEGMENT1, rta.SEGMENT2, rst.SALESREP_ID, jrdv.RESOURCE_NAME
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav, HZ_SITE_USES_V hsv, HZ_CUST_SITE_USES_ALL hcs,
RA_TERRITORIES rta, ra_salesrep_territories rst, jtf_rs_srp_vl jrsv, JTF_RS_DEFRESOURCES_VL jrdv
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
and aav.ADDRESS_ID = hsv.ADDRESS_ID
and hsv.SITE_USE_ID = hcs.SITE_USE_ID
and hcs.TERRITORY_ID = rta.TERRITORY_ID
and hsv.SITE_USE_CODE = ‘BILL_TO’
and hcs.TERRITORY_ID = rst.TERRITORY_ID
and jrsv.SALESREP_ID = rst.SALESREP_ID
and jrdv.RESOURCE_ID = jrsv.RESOURCE_ID
order by acv.CUSTOMER_NUMBER
12. Modifier Deddy =
SELECT ta.NAME, ba.start_date_active header_start,
ba.end_date_active header_end, ba.active_flag, qpa.product_attr_value
,(select mti.segment1 from mtl_system_items mti where mti.inventory_item_id = qpa.product_attr_value AND mti.ORGANIZATION_ID = 106 and qpa.PRODUCT_ATTRIBUTE =’PRICING_ATTRIBUTE1′ ) ITEM_NUMBER
,qpa.PRODUCT_UOM_CODE, qpa.PRICING_ATTR_VALUE_FROM_NUMBER QTY_FROM, qpa.PRICING_ATTR_VALUE_TO_NUMBER QTY_TO
,(select mct.DESCRIPTION from mtl_categories_tl mct where mct.CATEGORY_ID = qpa.product_attr_value and qpa.PRODUCT_ATTRIBUTE =’PRICING_ATTRIBUTE2′ ) ITEM_CATEGORY
, ql.start_date_active list_start, ql.end_date_active list_end, ql.automatic_flag, qpa.PRICING_ATTRIBUTE_ID
, qpa.PRODUCT_ATTRIBUTE, ql.ARITHMETIC_OPERATOR, ql.OPERAND,ql.ATTRIBUTE1 DISKON_OFF, qpa.LIST_LINE_id MODIFIER_NO
, (select qrm.FROM_RLTD_MODIFIER_ID from qp_rltd_modifiers qrm where qrm.TO_RLTD_MODIFIER_ID = qpa.LIST_LINE_id ) PARENT_NO
, ql.benefit_qty, ql.BENEFIT_UOM_CODE
,(select mti.segment1 from mtl_system_items mti where mti.inventory_item_id = qpa.product_attr_value AND mti.ORGANIZATION_ID = 104 and ql.OPERAND=100 ) ITEM_GET
, ql.LIST_LINE_TYPE_CODE, qq.COMPARISON_OPERATOR_CODE , qq.QUALIFIER_ATTR_VALUE Cust_ID
, qq.QUALIFIER_ATTRibute
,(select hp.PARTY_NAME from hz_parties hp,hz_cust_accounts hca where hp.PARTY_ID = hca.PARTY_ID and to_char(hca.CUST_ACCOUNT_ID) = qq.QUALIFIER_ATTR_VALUE ) CUST_NAME
,(select hl.ADDRESS1 from hz_party_sites hps,hz_locations hl where hps.LOCATION_ID = hl.LOCATION_ID and to_Char(hps.PARTY_SITE_ID) = qq.QUALIFIER_ATTR_VALUE) CUST_NAME_SITE
FROM qp_list_headers_b ba,
qp_list_headers_tl ta,
qp_list_lines ql,
qp_pricing_attributes qpa,
qp_qualifiers qq
– qp_rltd_modifiers qrm
WHERE ql.list_header_id = ta.list_header_id
AND ba.list_header_id = ta.list_header_id
AND ba.list_type_code ‘PRL’
AND ql.list_line_id = qpa.list_line_id
AND (ba.end_date_active >= to_date(‘28/jan/2008′) or ba.end_date_active is NULL)
–AND ql.LIST_HEADER_ID = qq.LIST_HEADER_ID
AND ql.LIST_LINE_ID = qq.LIST_LINE_ID(+)
– AND qpa.product_attr_value = ‘845′
– AND qpa.list_line_id = qrm.from_rltd_modifier_id (+)
– AND qq.QUALIFIER_ATTRIBUTE ‘QUALIFIER_ATTRIBUTE2′
ORDER BY qpa.LIST_LINE_id
SELECT a.list_header_id, a.NAME, a.version_no, b.start_date_active START_HEAD,
b.end_date_active END_HEAD, b.active_flag,
c.modifier_level, c.list_line_type, c.price_break_type,
c.charge_name, c.formula, c.arithmetic_operator_type, c.operand,
c.arithmetic_operator, c.start_date_active START_LINE, c.end_date_active END_LINE,
c.incompatibility_grp_code, c.list_line_no, c.product_precedence,
c.pricing_attribute_id, c.product_attribute_context, c.product_attr,
c.product_attr_val, c.product_uom_code, c.comparison_operator_code,
c.pricing_attribute_context, c.pricing_attr, c.pricing_attr_value_to,
c.pricing_attr_seg_name, c.prod_attr_segment_name,
c.product_attribute_type, c.product_attr_value, c.pricing_attribute,
d.qualifier_id, d.excluder_flag, d.comparision_operator_code,
d.qualifier_context, d.qualifier_attribute, d.qualifier_grouping_no,
d.qualifier_attr_value, d.start_date_active START_QUALIFIER, d.end_date_active END_QUALIFIER,
d.qualifier_datatype, d.qualifier_precedence, e.customer_name
FROM qp_list_headers_tl a,
qp_list_headers_b b,
qp_modifier_summary_v c,
qp_qualifiers_v d,
ar_customers_v e
WHERE a.list_header_id = b.list_header_id
AND a.list_header_id = c.list_header_id
AND b.list_header_id = c.list_header_id
AND c.list_line_id = d.list_line_id(+)
AND e.customer_id(+) = d.qualifier_attr_value
2. Query MAster Item (Inventory) =
SELECT a.segment1 no_dok,
DECODE (a.status_lookup_code,
‘I’, ‘Incomplete’,
‘A’, ‘Active’,
‘Closed’
) status_quo,
b.vendor_name, c.vendor_site_code, f.INVENTORY_ITEM_ID, f.segment1 ko_bar, f.attribute2 kode_foxpro,
d.item_description, f.PRIMARY_UOM_CODE, d.unit_price hna, e.quantity, e.price_override hjp,
e.price_discount marjin, g.category_concat_segs kls_prod,
h.ATTRIBUTE4 PHB, f.HAZARD_CLASS_ID OKT, i.LOCATION_CODE
FROM po_headers_all a,
po_vendors b,
po_vendor_sites_all c,
po_lines_all d,
po_line_locations_all e,
mtl_system_items_b f,
mtl_item_categories_v g,
mtl_system_items_b h,
hr_locations_all i
WHERE a.type_lookup_code = ‘QUOTATION’
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND a.status_lookup_code ‘C’
AND a.closed_date IS NULL
AND a.po_header_id = d.po_header_id
AND a.po_header_id = e.po_header_id
AND d.po_line_id = e.po_line_id
AND d.item_id = f.inventory_item_id
AND d.item_id = g.inventory_item_id
AND f.inventory_item_id = g.inventory_item_id
AND f.organization_id = ‘105′
and h.INVENTORY_ITEM_ID = f.INVENTORY_ITEM_ID
and h.ORGANIZATION_ID = ‘104′
AND f.organization_id = g.organization_id
and i.LOCATION_ID = e.SHIP_TO_LOCATION_ID
AND g.category_set_id = 1
AND d.po_line_id IN (
SELECT z.po_line_id
FROM (SELECT b.vendor_name, c.vendor_site_code,
MAX (d.po_line_id) po_line_id, f.segment1,
d.item_description
FROM po_headers_all a,
po_vendors b,
po_vendor_sites_all c,
po_lines_all d,
po_line_locations_all e,
mtl_system_items_b f
WHERE a.type_lookup_code = ‘QUOTATION’
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND a.status_lookup_code ‘C’
AND a.closed_date IS NULL
AND a.po_header_id = d.po_header_id
AND a.po_header_id = e.po_header_id
AND d.po_line_id = e.po_line_id
AND d.item_id = f.inventory_item_id
AND f.organization_id = ‘105′
GROUP BY b.vendor_name,
c.vendor_site_code,
f.segment1,
d.item_description) z)
3. Query Locator =
SELECT d.segment1, b.organization_name, a.transaction_quantity,
a.subinventory_code,
c.segment1
|| ‘.’
|| c.segment2
|| ‘.’
|| c.segment3
|| ‘.’
|| c.segment4 LOCATOR,
c.description, a.lot_number
FROM mtl_onhand_quantities a,
org_organization_definitions b,
mtl_item_locations c,
mtl_system_items_b d
WHERE a.organization_id = b.organization_id
AND a.locator_id = c.inventory_location_id(+)
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
ORDER BY d.segment1
4. Query Status =
select
c.NAME,
a.HEADER_ID,
a.ORDER_NUMBER,
d.NAME,
e.name,
a.ORDERED_DATE,
b.CUSTOMER_NAME,
a.FLOW_STATUS_CODE
from OE_ORDER_HEADERS_all a,
ar_customers_v b,
hr_organization_units c,
oe_transaction_types_tl d,
qp_list_headers_tl e,
where a.SOLD_TO_ORG_ID = b.CUSTOMER_ID
and a.ORG_ID = c.ORGANIZATION_ID
and a.ORDER_TYPE_ID = d.TRANSACTION_TYPE_ID
and e.LIST_HEADER_ID = a.PRICE_LIST_ID
and a.ORDER_NUMBER = ‘130109000352′
5. Query SO =
SELECT DISTINCT a.ct_reference, a.trx_number, a.trx_date,
a.interface_header_context sumber, b.customer_name,
c.NAME tipe_transaksi, d.NAME organisasi,
e.customer_trx_line_id, e.line_number, e.quantity_invoiced,
e.unit_selling_price, e.extended_amount, e.revenue_amount,
e.uom_code, e.line_type, e.description,
e.link_to_cust_trx_line_id
FROM ra_customer_trx_all a,
ar_customers_v b,
ra_cust_trx_types_all c,
hr_organization_units d,
ra_customer_trx_lines_all e,
mtl_system_items_b f
WHERE a.sold_to_customer_id = b.customer_id
AND a.cust_trx_type_id = c.cust_trx_type_id
AND a.org_id = d.organization_id
AND a.customer_trx_id = e.customer_trx_id
AND e.inventory_item_id = f.inventory_item_id(+)
6. Query Price list OM =
select qlht.DESCRIPTION, msi.ATTRIBUTE2, msi.DESCRIPTION , qppr.PRODUCT_UOM_CODE, qpll.OPERAND, qpll.START_DATE_ACTIVE, qpll.END_DATE_ACTIVE
from qp_list_headers_b qlhb,qp_list_headers_tl qlht,qp_list_lines qpll, qp_pricing_attributes qppr, MTL_SYSTEM_ITEMS msi
where qlhb.LIST_HEADER_ID = qlht.LIST_HEADER_ID
and qlhb.ACTIVE_FLAG = ‘Y’
and qlhb.END_DATE_ACTIVE is null
and qlhb.LIST_TYPE_CODE = ‘PRL’
and qpll.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and qppr.LIST_LINE_ID = qpll.LIST_LINE_ID
and qppr.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qppr.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘104′
7. Query Customer =
SELECT hps.PARTY_SITE_NUMBER CUSTOMER_NUMBER, hcasa.CUST_ACCOUNT_ID SOLD_TO_ORG_ID, hl.ADDRESS1 NAME
FROM hz_party_sites hps, hz_locations hl, hz_cust_acct_sites_all hcasa
WHERE hl.location_id = hps.location_id and hcasa.party_site_id = hps.party_site_id
ORDER BY hps.party_site_number
8. Query GL =
SELECT (CASE WHEN ABSA.product_precedence IN (210, 230, 220) THEN
(CASE
WHEN otta.order_category_code = ‘RETURN’
THEN (opa.adjusted_amount_per_pqty)
* oola.ordered_quantity
ELSE ABS (opa.adjusted_amount_per_pqty)
* oola.ordered_quantity
END
)
ELSE
(CASE
WHEN otta.order_category_code = ‘RETURN’
THEN (((absa.ATTRIBUTE1*OOLA.UNIT_LIST_PRICE)/100)*OOLA.ORDERED_QUANTITY)
ELSE ABS ((((absa.ATTRIBUTE1*OOLA.UNIT_LIST_PRICE)/100)*OOLA.ORDERED_QUANTITY))
END
)
END) discount_amount,
msi.sales_account ccid, rcta.trx_number, rcta.trx_date,
rcta.invoice_currency_code curr, ooha.order_number,
absa.product_precedence, oola.line_id, oola.ROWID rid
–, gcc.segment1, gcc.segment2,
–gcc.segment3, gcc.segment4, gcc.segment5, gcc.segment6
FROM oe_order_lines_all oola,
ra_customer_trx_all rctad,
oe_order_headers_all ooha,
mtl_system_items msi,
oe_price_adjustments opa,
qp_list_headers_tl qlht,
ra_customer_trx_all rcta,
hr_organization_units hou,
so_order_types_all sota,
oe_transaction_types_all otta,
–ra_customer_trx_lines_all rctla,
–gl_code_combinations gcc,
(SELECT /*+ USE_HASH(QLL,QLTB) */
DISTINCT qlt.list_header_id, qll.product_precedence, qll.ATTRIBUTE1
FROM qp_list_headers_tl qlt,
qp_list_headers_b qltb,
qp_list_lines qll
WHERE qll.list_header_id = qlt.list_header_id
AND qll.product_precedence IS NOT NULL
AND qll.product_precedence IN (210, 230, 220, 260,250,290)
AND qltb.list_header_id = qlt.list_header_id
AND qltb.active_flag ‘N’) absa
WHERE msi.organization_id = hou.organization_id
–AND rctla.interface_line_attribute6 = oola.line_id
–AND rctla.customer_trx_id = rcta.customer_trx_id
–AND rctla.inventory_item_id = msi.inventory_item_id
AND rcta.previous_customer_trx_id = rctad.customer_trx_id(+)
–MASTER_ITEM
AND oola.inventory_item_id = msi.inventory_item_id
AND opa.list_header_id = qlht.list_header_id
AND opa.line_id = oola.line_id
AND UPPER (qlht.list_header_id) = absa.list_header_id
–DISCOUNT
AND ooha.header_id = oola.header_id
AND rcta.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND oola.flow_status_code = ‘CLOSED’
AND hou.organization_id = ooha.ship_from_org_id
AND sota.NAME = rcta.interface_header_attribute2
AND sota.order_type_id = ooha.order_type_id
/*AND TO_DATE (rcta.trx_date) BETWEEN TO_DATE
(:p_from_date,
‘YYYY/MM/DD HH24:MI:SS’
)
AND TO_DATE
(:p_to_date,
‘YYYY/MM/DD HH24:MI:SS’
)*/
–PARAMETER DATE
–AND ooha.org_id = :p_org_id
AND otta.transaction_type_id = ooha.order_type_id
AND oola.attribute15 IS NULL;
9. Sales Teritory =
select * from JTF_RS_DEFRESOURCES_VL
RESOURCE_NUMBER
select a.SALESREP_TERRITORY_ID,a.SALESREP_ID,a.TERRITORY_ID ,b.SALESREP_NUMBER
from ra_salesrep_territories a, jtf_rs_salesreps b ,
where a.SALESREP_ID = b.SALESREP_ID;
select SALESREP_ID,NAME,SALESREP_NUMBER from jtf_rs_salesreps
select TERRITORY_ID,name,DESCRIPTION from RA_TERRITORIES
select a.SALESREP_TERRITORY_ID,a.SALESREP_ID,a.TERRITORY_ID ,b.SALESREP_NUMBER
from ra_salesrep_territories a, jtf_rs_salesreps b ,
where a.SALESREP_ID = b.SALESREP_ID;
10.Harga & Customer =
BEGIN
dbms_application_info.set_client_info(103);
end;
select acv.CUSTOMER_NAME, acv.CUSTOMER_NAME_PHONETIC, acv.CUSTOMER_NUMBER,aav.ADDRESS_ID,
aav.PARTY_ID, acv.CUSTOMER_ID,
aav.PARTY_SITE_ID,aav.ADDRESS_LINES_PHONETIC
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
select qlht.DESCRIPTION, msi.ATTRIBUTE2, msi.DESCRIPTION , qppr.PRODUCT_UOM_CODE, qpll.OPERAND, qpll.START_DATE_ACTIVE, qpll.END_DATE_ACTIVE
from qp_list_headers_b qlhb,qp_list_headers_tl qlht,qp_list_lines qpll, qp_pricing_attributes qppr, MTL_SYSTEM_ITEMS msi
where qlhb.LIST_HEADER_ID = qlht.LIST_HEADER_ID
and qlhb.ACTIVE_FLAG = ‘Y’
and qlhb.END_DATE_ACTIVE is null
and qlhb.LIST_TYPE_CODE = ‘PRL’
and qpll.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and qppr.LIST_LINE_ID = qpll.LIST_LINE_ID
and qppr.LIST_HEADER_ID = qlhb.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qppr.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘104′
11. Query OCHIE =
select otl.NAME, otl.DESCRIPTION
from OE_TRANSACTION_TYPES_ALL ott, OE_TRANSACTION_TYPES_TL otl
where ott.ORG_ID = ‘204′
and ott.TRANSACTION_TYPE_ID = otl.TRANSACTION_TYPE_ID
order by otl.NAME
select ooh.ORDER_NUMBER
from OE_TRANSACTION_TYPES_ALL ott, OE_ORDER_HEADERS_ALL ooh, OE_TRANSACTION_TYPES_TL otl
where ott.ORG_ID = ‘204′
and ott.TRANSACTION_TYPE_ID = ooh.ORDER_TYPE_ID
and otl.TRANSACTION_TYPE_ID = ooh.ORDER_TYPE_ID
and ott.TRANSACTION_TYPE_ID = otl.TRANSACTION_TYPE_ID
order by ooh.ORDER_NUMBER
- aDDitiOnaL SCRipt -
ott.ORG_ID = :$PROFILES$.ORG_ID
:$FLEX$.nama value set
- QueRy PRiCe LiSt -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_secu_list_headers_v qsl, qp_list_lines_v qll
where qsl.ORIG_ORG_ID = ‘107′
and qsl.LIST_HEADER_ID = ‘7064′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
order by qll.PRODUCT_ATTR_VAL_DISP
- UpDatE QueRy PRiCe LiSt -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_list_headers_v qsl, qp_list_lines_v qll
where qsl.ORIG_ORG_ID = ‘107′
and qsl.LIST_HEADER_ID = ‘7064′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
order by qll.PRODUCT_ATTR_VAL_DISP
select qll.PRODUCT_ATTRIBUTE_CONTEXT, qll.PRODUCT_ATTR_VAL_DISP, msi.DESCRIPTION,
qll.PRODUCT_UOM_CODE,
qll.OPERAND, qll.PRODUCT_PRECEDENCE, qll.START_DATE_ACTIVE,
qll.END_DATE_ACTIVE
from qp_list_headers_v qsl, qp_list_lines_v qll, MTL_SYSTEM_ITEMS_B msi
where qsl.ORIG_ORG_ID = ‘103′
and qsl.LIST_HEADER_ID = ‘6041′
and qsl.LIST_HEADER_ID = qll.LIST_HEADER_ID
and msi.INVENTORY_ITEM_ID = qll.PRODUCT_ATTR_VALUE
and msi.ORGANIZATION_ID = ‘106′
and substr(PRODUCT_ATTR_VAL_DISP,2,1) = ‘R’
order by qll.PRODUCT_ATTR_VAL_DISP
- QueRy ORIG_SYSTEM_REFERENCE -
BEGIN
dbms_application_info.set_client_info(107);
end;
select acv.CUSTOMER_NUMBER, acv.CUSTOMER_NAME, acv.ORIG_SYSTEM_REFERENCE,
aav.CONCATENATED_ADDRESS, aav.ORIG_SYSTEM_REFERENCE
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
- QueRy teRRitORieS -
BEGIN
dbms_application_info.set_client_info(107);
end;
select acv.CUSTOMER_NUMBER, rta.NAME, rta.SEGMENT1, rta.SEGMENT2
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav, HZ_SITE_USES_V hsv, HZ_CUST_SITE_USES_ALL hcs,
RA_TERRITORIES rta
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
and aav.ADDRESS_ID = hsv.ADDRESS_ID
and hsv.SITE_USE_ID = hcs.SITE_USE_ID
and hcs.TERRITORY_ID = rta.TERRITORY_ID
order by acv.CUSTOMER_NUMBER
- QueRy mODiFieR nUmbeR -
BEGIN
dbms_application_info.set_client_info(107);
end;
select qms.LIST_LINE_ID, qms.OPERAND, qms.LIST_LINE_NO, qms.PRODUCT_ATTR_VALUE
from qp_modifier_summary_v qms
where qms.LIST_HEADER_ID = ‘7133′
_aDDitiOnaL SCRipt_
SELECT ffvt.description
INTO v_desc
FROM fnd_flex_value_sets ffvs, fnd_flex_values ffv, fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_name = ‘MPI_Assisten Apoteker’
AND ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffvt.flex_value_id = ffv.flex_value_id
AND ffv.enabled_flag = ‘Y’
AND ffvt.flex_value_meaning = :attribute3;
- QueRy ItEmS -
select msi.SEGMENT1, msi.DESCRIPTION, msi.WEIGHT_UOM_CODE, msi.PRIMARY_UNIT_OF_MEASURE,
msi.PRIMARY_UOM_CODE
from MTL_SYSTEM_ITEMS_B msi
where msi.ORGANIZATION_ID = ‘108′
and msi.INVENTORY_ITEM_STATUS_CODE = ‘Active’
and substr(SEGMENT1,2,1) = ‘B’
order by msi.SEGMENT1
- QueRy CuStOmeR -
select acv.CUSTOMER_NAME, acv.CUSTOMER_NAME_PHONETIC, acv.CUSTOMER_NUMBER,aav.ADDRESS_ID,
aav.PARTY_ID,
aav.PARTY_SITE_ID,aav.ADDRESS_LINES_PHONETIC
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
order by acv.CUSTOMER_NAME
- QueRy SaLeS PeRSon -
BEGIN
dbms_application_info.set_client_info(103);
end;
select acv.CUSTOMER_NUMBER, rta.NAME, rta.SEGMENT1, rta.SEGMENT2, rst.SALESREP_ID, jrdv.RESOURCE_NAME
from AR_CUSTOMERS_V acv, AR_ADDRESSES_V aav, HZ_SITE_USES_V hsv, HZ_CUST_SITE_USES_ALL hcs,
RA_TERRITORIES rta, ra_salesrep_territories rst, jtf_rs_srp_vl jrsv, JTF_RS_DEFRESOURCES_VL jrdv
where acv.CUSTOMER_ID = aav.CUSTOMER_ID
and aav.ADDRESS_ID = hsv.ADDRESS_ID
and hsv.SITE_USE_ID = hcs.SITE_USE_ID
and hcs.TERRITORY_ID = rta.TERRITORY_ID
and hsv.SITE_USE_CODE = ‘BILL_TO’
and hcs.TERRITORY_ID = rst.TERRITORY_ID
and jrsv.SALESREP_ID = rst.SALESREP_ID
and jrdv.RESOURCE_ID = jrsv.RESOURCE_ID
order by acv.CUSTOMER_NUMBER
12. Modifier Deddy =
SELECT ta.NAME, ba.start_date_active header_start,
ba.end_date_active header_end, ba.active_flag, qpa.product_attr_value
,(select mti.segment1 from mtl_system_items mti where mti.inventory_item_id = qpa.product_attr_value AND mti.ORGANIZATION_ID = 106 and qpa.PRODUCT_ATTRIBUTE =’PRICING_ATTRIBUTE1′ ) ITEM_NUMBER
,qpa.PRODUCT_UOM_CODE, qpa.PRICING_ATTR_VALUE_FROM_NUMBER QTY_FROM, qpa.PRICING_ATTR_VALUE_TO_NUMBER QTY_TO
,(select mct.DESCRIPTION from mtl_categories_tl mct where mct.CATEGORY_ID = qpa.product_attr_value and qpa.PRODUCT_ATTRIBUTE =’PRICING_ATTRIBUTE2′ ) ITEM_CATEGORY
, ql.start_date_active list_start, ql.end_date_active list_end, ql.automatic_flag, qpa.PRICING_ATTRIBUTE_ID
, qpa.PRODUCT_ATTRIBUTE, ql.ARITHMETIC_OPERATOR, ql.OPERAND,ql.ATTRIBUTE1 DISKON_OFF, qpa.LIST_LINE_id MODIFIER_NO
, (select qrm.FROM_RLTD_MODIFIER_ID from qp_rltd_modifiers qrm where qrm.TO_RLTD_MODIFIER_ID = qpa.LIST_LINE_id ) PARENT_NO
, ql.benefit_qty, ql.BENEFIT_UOM_CODE
,(select mti.segment1 from mtl_system_items mti where mti.inventory_item_id = qpa.product_attr_value AND mti.ORGANIZATION_ID = 104 and ql.OPERAND=100 ) ITEM_GET
, ql.LIST_LINE_TYPE_CODE, qq.COMPARISON_OPERATOR_CODE , qq.QUALIFIER_ATTR_VALUE Cust_ID
, qq.QUALIFIER_ATTRibute
,(select hp.PARTY_NAME from hz_parties hp,hz_cust_accounts hca where hp.PARTY_ID = hca.PARTY_ID and to_char(hca.CUST_ACCOUNT_ID) = qq.QUALIFIER_ATTR_VALUE ) CUST_NAME
,(select hl.ADDRESS1 from hz_party_sites hps,hz_locations hl where hps.LOCATION_ID = hl.LOCATION_ID and to_Char(hps.PARTY_SITE_ID) = qq.QUALIFIER_ATTR_VALUE) CUST_NAME_SITE
FROM qp_list_headers_b ba,
qp_list_headers_tl ta,
qp_list_lines ql,
qp_pricing_attributes qpa,
qp_qualifiers qq
– qp_rltd_modifiers qrm
WHERE ql.list_header_id = ta.list_header_id
AND ba.list_header_id = ta.list_header_id
AND ba.list_type_code ‘PRL’
AND ql.list_line_id = qpa.list_line_id
AND (ba.end_date_active >= to_date(‘28/jan/2008′) or ba.end_date_active is NULL)
–AND ql.LIST_HEADER_ID = qq.LIST_HEADER_ID
AND ql.LIST_LINE_ID = qq.LIST_LINE_ID(+)
– AND qpa.product_attr_value = ‘845′
– AND qpa.list_line_id = qrm.from_rltd_modifier_id (+)
– AND qq.QUALIFIER_ATTRIBUTE ‘QUALIFIER_ATTRIBUTE2′
ORDER BY qpa.LIST_LINE_id
