How to differentiate customer and suppliers data in hz_parties table?

SELECT   *
    FROM   (SELECT   hp.party_number,
                     hp.party_name,
                     hp.status,
                     DECODE (NVL (hpu.party_usage_code, hp.party_type),
                             'ORGANIZATION', 'CUSTOMER',
                             NVL (hpu.party_usage_code, hp.party_type))
                        party_type
              FROM   hz_party_usg_assignments hpu, hz_parties hp
             WHERE   hp.party_id = hpu.party_id(+))
ORDER BY   party_type;

Difference between a Stored Procedure and a Trigger?

1. We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined.
2. We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which the action (insert, delete, and update) defined within a trigger can initiate the execution of another trigger defined on the same table or a different table.
3. Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.
4. Stored the procedure can take input parameters, but we can't pass parameters as input to a trigger.
5. Stored procedures can return values but a trigger cannot return a value.
6. We can use Print commands inside a stored procedure for debugging purposes but we can't use print commands inside a trigger.
7. We can use transaction statements like begin the transaction, commit the transaction, and rollback inside a stored procedure but we can't use transaction statements inside a trigger.
8. We can call a stored procedure from the front end (.asp files, .aspx files, .ascx files, etc.) but we can't call a trigger from these files.
9. Stored procedures are used for performing tasks. They can have parameters and return multiple result sets.
10. Triggers normally are used for auditing work. They can be used to trace the activities of table events.

Difference between Procedure and Function?

What is difference between Package and Stored Procedure?

Package:

• A package is a group of PL/SQL types, objects, stored procedures, and functions. it has two parts one is specification other is the body.
• In the specification, we mention procedures, functions & their parameters.
• In the body part, we define the whole operation performed by procedures and functions mentioned in the specification part.
• You can make your procedure private to the package by not declaring it in the package specification.
• packages cannot be called, passed parameters, or nested
• While calling a procedure from a package whole of the package is loaded into the memory. Like if a package consists of 4 procedures & we call 1 procedure then the whole 4 would be loaded to memory.

Procedure:

• The procedure is a standalone pl/sql unit in which all things related to procedure define in one go i.e parameters and whole functionality etc
• A procedure is a stored program in an oracle that is written down when a particular task has to be done.
• A procedure that resides in a package has to be called as <package_name>.<procedure_name>. On the other hand, a standalone procedure can be called by its name alone.
• We can pass IN, OUT parameters in the procedure.

Query to Find the Price list name for Item in Oracle Apps R12

SELECT qph.name

, msi.segment1

, qpl.operand

, qpl.product_precedence

FROM  qp_list_headers qph,

apps.qp_list_lines_v qpl,

inv.mtl_system_items_b msi

WHERE  qph.list_header_id = qpl.list_header_id

and qpl.product_attr_value = to_char(msi.inventory_item_id)

and msi.ORGANIZATION_ID =4

and msi.segment1 ='RED0126MCO';